Shrinking the transaction logs for a SQL Server database

Overview

Bare in mind that shrinking log files is not the same as compressing SQL Backups. It's important to note this, because if you Google for each, you'll return different results.

Compressing SQL Backups is the act of compressing *.bak files that are backups of the database itself.

Shrinking SQL Log files is the act of removing transaction logs that you don't want anymore; essentially, you are removing unwanted transactions from the database's .LDF file.

I've worked on a few projects that pull in several million visitors per week, where the database server has hardly any disk space left due to large transaction log files that don't need to be there in this business case.

FYI, I gathered the information to do this from this Microsoft article: http://support.microsoft.com/kb/907511

The process

There are two commands that have to be executed, which are explained below with an example database.

1) backup log dbTestDatabase to disk = 'C:\SQL\Backups\dbTestDatabaseLogs.bak'

This command is backing up your transaction log to a .bak file. In the process of doing this, the virtual logs that are in use by the DMBS are freed up after they're backed up. This then allows you to do step 2.

2) DBCC SHRINKFILE ('dbTestDatabase_Log', 1) WITH NO_INFOMSGS

This means that you're going to shrink the logical name for the dbTestDatabase.ldf file to 1mb and you don't want messages back. To get this name, right-click on your database, and choose properties. Then click "Files", and your logical name is shown on the row with the file type of "log".

This command removes the virtual logs that you've just backed up. If you run this command without running the backup first, you won't get the LDF file down to your desired size, as the DBMS will still be using a lot of the transactions you're trying to remove.

If you still can't get the log file down to your desired file size, a few virtual transactions may have piled up in the meantime, so run step one again, and then re-run step 2 quickly afterwards.