DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK

Question

I've got a database, [My DB], that has the following info:
SQL Server 2008
MDF size: 30 GB
LDF size: 67 GB

I wanted to shrink the log file as much as possible and so I started my quest to figure out how to do this. Caveat: I am not a DBA (or even approaching a DBA) and have been progressing by feel through this quest.

First, I just went into SSMS, DB properties, Files, and edited the Initial Size (MB) value to 10. That reduced the log file to 62 GB (not exactly the 10 MB that I entered). So, I attached SQL Profiler, saw that DBCC SHRINKFILE was being called. I then entered that command into the query editor and here's the results.

DBCC SHRINKFILE (N'My DB_Log' , 10) 

And the output was:

Cannot shrink log file 2 (My DB_Log) because the logical log file located at the end of the file is in use. DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages ------ ----------- ----------- ----------- ----------- -------------- 8      2           8044104     12800       8044104     12800  (1 row(s) affected)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

I then did some research on that and found this:

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

Which says that I need to backup the log file before the shrinkfile so that the virtual log files will be released and the shrinkfile can do its job - I don't know what that means... I'm just paraphrasing here :)

So, I figured I'd try to backup the log file and then do a DBCC SHRINKFILE (and I changed the new log file size to 12800 since that was the MinimumSize identified in the output of the previous DBCC SHRINKFILE command)

BACKUP LOG [My DB] TO DISK = 'D:\SQLBackup\20110824-MyDB-Log.bak' GO DBCC SHRINKFILE (N'My DB_Log' , 12800) GO 

The result was the same as the first go around. I can only get the log file down to 62 GB.

I'm not sure what I'm doing wrong and what I should try next.

Answer 1

Okay, here is a solution to reduce the physical size of the transaction file, but without changing the recovery mode to simple.

Within your database, locate the file_id of the log file using the following query.

SELECT * FROM sys.database_files; 

In my instance, the log file is file_id 2. Now we want to locate the virtual logs in use, and do this with the following command.

DBCC LOGINFO; 

Here you can see if any virtual logs are in use by seeing if the status is 2 (in use), or 0 (free). When shrinking files, empty virtual logs are physically removed starting at the end of the file until it hits the first used status. This is why shrinking a transaction log file sometimes shrinks it part way but does not remove all free virtual logs.

If you notice a status 2's that occur after 0's, this is blocking the shrink from fully shrinking the file. To get around this do another transaction log backup, and immediately run these commands, supplying the file_id found above, and the size you would like your log file to be reduced to.

-- DBCC SHRINKFILE (file_id, LogSize_MB) DBCC SHRINKFILE (2, 100); DBCC LOGINFO; 

This will then show the virtual log file allocation, and hopefully you'll notice that it's been reduced somewhat. Because virtual log files are not always allocated in order, you may have to backup the transaction log a couple of times and run this last query again; but I can normally shrink it down within a backup or two.