SQL Server transaction log cannot shrink

This morning, an email alert said one of our Database server disk space is beyond the limit. After the checking, found it is due to one database transaction log was growing to 3 times than regular. So it needs to shrink, however, no matter how I shrink, the transaction log, ldf file still kept its size.
You can reference my previous post to analyses of the shrink log issue, but here is an quick solution.

1. Check the status of database
run script as below
select name, log_reuse_wait_desc from sys.databases where name=’mydatabase’

and the value is “LOG_BACKUP”
so it means, this database is full recovery mode and waiting for a transaction log backup.

2. Truncate transaction log
run the script as below
BACKUP LOG mydatabase WITH truncate_only
got the error: “‘truncate_only’ is not a recognized BACKUP option.”
OHOH, seems this command deprecated in SQL Server 2008R2 and later version

then run the following:

it works, and then shrink the database ldf file, shrink successfully!

BTW, there is a trick: the upper process may need to repeat a few times to get the best result. The first round, it may just shrink a little bit, but repeat those steps in second round, then the transaction log size could shrink to 1M.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s