Maybe your users cannot access your database, or maybe your preferred monitoring software is reporting limited or non-existent disk space on your log drive for a particular SQL Server.  Maybe you get a call from a Subject Matter Expert telling you their application is wigging out, or maybe you get a call from a user or even better, from your helpdesk.  We have all gotten that phone call or e-mail at one point or another.  A transaction log has gone awry and wreaked havoc on your limited drive allocated to the log files.  What next?

Do we know the database?  We could look at the drive and find the culprit.  However, we will need the logical name of the log file in order to shrink it, remember it isn’t always the same as the filename.  Run the following in SQL Server Management Studio (SSMS) in order to get the log file name:

SELECT name FROM sys.database_files WHERE type_desc = ‘LOG’

At this point, we can try to shrink the database by issuing the following command:

DBCC SHRINKFILE(‘<DB log file name>’, 0) 

If this does not shrink the file, then run the following command to see the log_reuse_wait_desc column (way over to the right):

SELECT * FROM sys.databases WHERE name = ‘<DB Name>’

The following values of the log_reuse_wait_desc are the most common reasons that the shrink operation has failed:

1. CHECKPOINT:  This means that there is no database checkpoint in the transaction logs.  You should manually run the CHECKPOINT command or you could also perform a database backup.
2. ACTIVE_TRANSACTION:  This indicates that there is an open transaction.  You can use DBCC OPENTRAN to find out what is going on (I will write another blog on this soon and link back here).
3. LOG_BACKUP:  This indicates that the log file has wrapped around to the beginning of the file and the free space is somewhere in the middle of the file.  Run another log backup in order to move the free space to the beginning or end of the log file.

After completing one of the three afore-mentioned steps, we can now issue the SHRINKFILE command again….And all will be well with the universe.  Enjoy!