You can shrink it only if you have space left to shrink. You can find out how much is used and how much is free using:SELECT *
FROM sys.dm_os_performance_counters dopc
WHERE dopc.counter_name IN
(
'Log File(s) Size (KB)',
'Log File(s) Used Size (KB)',
'Percent Log USED'
)
AND dopc.instance_name = 'YourDatabaseName'
If there is not much free space and if you have not been doing it you should do a full database backup and schedule regular log backups. Then, see how much space is in the log file again. If it still shows very little free space, look at the log_reuse_wait_desc column in sys.databases. It can be due to a number of reasons - see here: http://msdn.microsoft.com/en-us/library/ms345414(v=sql.105).aspx You will need to address that before you will be able to shrink the log file.
Another option that people sometimes use is to change the recovery model to simple and then change it back to full or bulk-logged. This will cause the log sequence chain to be broken, but if you don't care about point in time recovery, you probably can do this. But I am not recommending this, since I don't know your server or business requirements.
Please keep in mind is that while it may be okay to shrink the log file in this specific case, routinely shrinking log file is a very bad idea. http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
Database Transaction Log is huge
Posted - 02/24/2013 : 17:21:14
