Transaction Logs in MS SQL Server 2005

The other day I tried to find a solution for an excessively large transaction log file in one of our production systems. It grew to an astonishing 40 GB which set off some alarms. Our middleware service provider offered to take care of this problem for a rather high monthly fee that I didn't feel like paying: after all, how difficult could it be to shrink that stupid file and keep it under control?

Turns out it with a little trickier than I thought. I, actually, have never used MS SQL Server before and have never come across any transaction logs I had to take care off. In my previous server-based project we worked with MySQL which did not provide such a facility (and I shall stand correct if you show me where I missed it). So off I went to find some information about the transaction logs for MS SQL Server and, in theory, things were supposed to be easy: execute two SQL statements, one backing up the transaction log, the other shrinking the log file - that's it.

Backing up worked (with a 40 GB log it took quite some time, of course) like a charm but shrinking did not seem to work. Once I found the command

DBCC SQLPERF (logspace)

I was at least able to verify that the backup had worked and had really set the records in the log to inactive. After some more trial and error I found that I had just passed the wrong parameter to shrinkfile: instead of the database name it expects the name of the log file (excluding the file extension.

A long story short, the following two commands executed in SQL Management Studio will successfully backup and shrink your transaction log file:

USE [database];
BACKUP LOG [database] TO DISK = '[filename]';
DBCC SHRINKFILE ([name of transaction log without extension or quotes], 1);

Here's a more tangible example:

USE BenDB;
BACKUP LOG BenDB TO DISK = 'C:\Backup\BenDB_log.bak';
DBCC SHRINKFILE (BenDB_log, 1);

Hope this can save you some time one of these days.