Physically shrinking SQL Transaction log files

This post describes the process of shrinking the SQL transaction log file size, without using the ‘cheat’ method of switching the recovery mode from ‘full, to ‘simple’ and back to ‘full’ again, thus creating a hole in your transaction logs effecting database recovery.

To summarise the steps, we are going to

• Locate the file_id or filename of the physical log file
• View the status of the virtual log files
• Backup the transaction log if required
• Perform a shrink command, and view the results to determine any further actions

Locating the file_id

To perform a shrink you will need to know the file_id of the physical transaction log file. This can be done by querying the following within your database.

SELECT file_id, name, physical_name FROM sys.database_files;

Viewing virtual log status

Now we want to locate which virtual logs are in use by using the following command.

DBCC LOGINFO;

Here we can see the state of each virtual log contained within the log file. The Status column shows whether the virtual log is empty ’0′, or in use ’2′.

When shrinking files, the empty virtual logs are physically removed from the end of the file moving forwards until it hits the first virtual log in use. The SQL server does not always use virtual logs in physical sequence, which is why shrinking a transaction log file sometimes shrinks it part way, but does not remove all free virtual logs which you might expect. Subsequent shrinks will therefore not free any more space as the last virtual log will be in use, even though the log file itself could appear mostly empty.

Detecting if a Transaction log backup is required

If you notice any 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, then immediately run the DBCC SHRINKFILE (file_id, LogSize_MB) command, supplying the file_id found above, and the size you would like your log file to be reduced to.

The below will attempt to shrink file_id 2 to 100MB, and then show the resulting virtual log allocation

DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;

Hopefully you’ll notice that it’s been reduced somewhat. Because virtual logs 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 get it shrink down to the chosen size within two backups. The key is to do this straight after a transaction backup has occurred, thus reducing the chance that a virtual log will be assigned in an awkward place.