Shrinking Transaction Logs in SQL Server 2005/2008 Using T-SQL

A tripping point of database development in SQL Server 2005 and 2008 tends to be the management of the transaction logs.  When you get into a fairly complicated transactional database design, developers don't often worry too much about the transaction logs -- that is, until, the number of unused pages grows exponentially to the point where the transaction log hits its disk space limitation (if it has one), or worse yet, peg most of the physical disk space available, not to mention give your database an enormous performance hindrance.  


These tasks ought to be taken care of by a DBA (routine transaction log backups/truncations, etc), but developers may not have the luxury of DBA assistance in their test environments.

You can, of course, use SQL Server Management Studio, to assist with shrinking the transaction logs to free up disk space, but there may be times when you need to shrink the transaction as part of your T-SQL stored procedure processes.  Sample code follows:

CREATE PROCEDURE [dbo].[usp_ShrinkTransactionLog]
as

Checkpoint
DBCC OPENTRAN(SampleDatabase)

DECLARE @wk_fileid INT
SELECT @wk_fileid = fileid  
FROM sysfiles
WHERE [name] = 'SampleDatabase_log'

DBCC SHRINKFILE (@wk_fileid)

GO

The stored procedure above calls DBCC OPENTRAN to give you information on any open transactions, and is optional.  It's more of a nice-to-have to see what's still ongoing in the database.  


The real meat of the stored procedure is grabbing the internal FileId of your database's log and then issuing a DBCC SHRINKFILE against it, essentially performing the same task as the Tasks --> Shrink --> Files on SSMS -- the code above does not specify a target size, so it will try to shrink to your transaction log's configured default size.


Note that, if your database is on a Full Recovery model, this sproc may not free up all the disk space that you'd want (with DBCC SHRINKFILE potentially coming back with a message saying it couldn't free up all the space requested).  This is because the Full Recovery model, by design, requires that you have regular backups of your logs.  Thus, it will keep all transaction logs in the virtual logs until they are backed up.  The act of backing up your transaction log will truncate it and free up even more space.  So, we can modify the above stored procedure to take this into account:

CREATE procedure [dbo].[usp_BackupAndShrinkTransactionLog]
as

Checkpoint
DBCC opentran(SampleDatabase)
BACKUP LOG SampleDatabase

DECLARE @wk_fileid INT
SELECT @wk_fileid = fileid  
FROM sysfiles
WHERE [name] = 'SampleDatabase_log'

DBCC SHRINKFILE (@wk_fileid)

GO

Note that the stored procedure code above assumes that you or your DBA have configured a default Backup Destination for your database.  You can modify the BACKUP LOG statement to dump the transaction log to a specific named Backup Device by adding " TO SampleBackupDevice_Log1" (BACKUP LOG(Sample Database) TO SampleBackupDevice_Log1)


Using either of these stored procedures as part of your T-SQL routines will help in keeping your transaction log sizes manageable.  Of course, do consult with your DBA's if you end up moving your test database to a production environment, as I'm sure they will wonder why the log files backups happen more frequently than they designed.  :-)


Additional info from MSDN: Shrinking the Transaction Log