Why can't I shrink a transaction log file, even after backup?

Answer 1

Have you tried from within SQL Server management studio with the GUI. Right click on the database, tasks, shrink, files. Select filetype=Log.

I worked for me a week ago.

Answer 2

Don't you need this

DBCC SHRINKFILE ('Wxlog0', 0)

Just be sure that you are aware of the dangers: see here: Do not truncate your ldf files!

And here Backup Log with Truncate_Only: Like a Bear Trap

Answer 3

Try to use target size you need insted of TRUNCATEONLY in DBCC:

DBCC SHRINKFILE ('Wxlog0', 1)

And check this to articles:

http://msdn.microsoft.com/en-us/library/ms189493(SQL.90).aspx

http://support.microsoft.com/kb/907511

Edit:

You can try to move allocated pages to the beginning of the log file first with

DBCC SHRINKFILE ('Wxlog0', NOTRUNCATE)

and after that

DBCC SHRINKFILE ('Wxlog0', 1)

Answer 4

Try creating another full backup after you backup the log w/ truncate_only (IIRC you should do this anyway to maintain the log chain). In simple recovery mode, your log shouldn't grow much anyway since it's effectively truncated after every transaction. Then try specifying the size you want the logfile to be, e.g.

-- shrink log file to c. 1 GB
DBCC SHRINKFILE (Wxlog0, 1000);

The TRUNCATEONLY option doesn't rearrange the pages inside the log file, so you might have an active page at the "end" of your file, which could prevent it from being shrunk.

You can also use DBCC SQLPERF(LOGSPACE) to make sure that there really is space in the log file to be freed.

Answer 5

Put the DB back into Full mode, run the transaction log backup (not just a full backup) and then the shrink.

After it's shrunk, you can put the DB back into simple mode and it txn log will stay the same size.

Answer 6

You cannot shrink a transaction log smaller than its initially created size.

Answer 7

If you set the recovery mode on the database in 2005 (don't know for pre-2005) it will drop the log file all together and then you can put it back in full recovery mode to restart/recreate the logfile. We ran into this with SQL 2005 express in that we couldn't get near the 4GB limit with data until we changed the recovery mode.

Answer 8

You may run into this problem if your database is set to autogrow the log & you end up with lots of virtual log files.
Run DBCC LOGINFO('databasename') & look at the last entry, if this is a 2 then your log file wont shrink. Unlike data files virtual log files cannot be moved around inside the log file.

You will need to run BACKUP LOG and DBCC SHRINKFILE several times to get the log file to shrink.

For extra bonus points run DBBC LOGINFO in between log & shirks

Answer 9

Thank you to everyone for answering.

We finally found the issue. In sys.databases, log_reuse_wait_desc was equal to 'replication'. Apparently this means something to the effect of SQL Server waiting for a replication task to finish before it can reuse the log space.

Replication has never been used on this DB or this server. We cleared the incorrect state by running sp_removedbreplication. After we ran this, backup log and dbcc shrinkfile worked just fine.

Definitely one for the bag-of-tricks.

Sources:

http://social.technet.microsoft.com/Forums/pt-BR/sqlreplication/thread/34ab68ad-706d-43c4-8def-38c09e3bfc3b

http://www.eggheadcafe.com/conversation.aspx?messageid=34020486&threadid=33890705

Answer 10

I've had the same issue in the past. Normally a shrink and a trn backup need to occur multiple times. In extreme cases I set the DB to "Simple" recovery and then run a shrink operation on the log file. That always works for me. However recently I had a situation where that would not work. The issue was caused by a long running query that did not complete, so any attempts to shrink were useless until I could kill that process then run my shrink operations. We are talking a log file that grew to 60 GB and is now shrunk to 500 MB.

Remember, as soon as you change from FULL to Simple recovery mode and do the shrink, dont forget to set it back to FULL. Then immediately afterward you must do a FULL DB backup.