Manage Database Transaction Logs + SQL Server 2005

Most likely, the root cause for the continuous transaction log growth is related to the database recovery model being set to 'full' for your user defined databases without issuing regularly scheduled transaction log backups. The full recovery model configuration is maintaining all of the before and after records in the transaction log until the transaction log is backed up. Most likely, you are not backing up the transaction log on a regular basis i.e. hourly, daily, etc. to support a high availability solution such as log shipping.

With these items in mind, it is time to make some changes to your SQL Server user defined databases and get your transaction logs to a manageable size. Let's walk through each of these steps one at a time. Keep in mind that since you are not performing transaction log backups that this process could be completed at an as needed time, but the preference would be during a maintenance window.

Note - These commands can be run in SQL Server management tool of your choice and replace the '[YourDatabaseNameHere]' text with the needed user defined database name.

First, review the transaction log size prior to the shrinking process.

USE [YourDatabaseNameHere]

GO

SELECT * FROM sysfilesWHERE name LIKE '%LOG%'

GO

Second, set the database recovery model to 'simple'.

USE [YourDatabaseNameHere]

GO

ALTER DATABASE [YourDatabaseNameHere] SET RECOVERY SIMPLE

GO

Third, issue a checkpoint against the database to write the records from the transaction log to the database.

USE [YourDatabaseNameHere]

GO

CHECKPOINT

GO

Fourth, truncate the transaction log.

USE [YourDatabaseNameHere]

GO

BACKUP LOG [YourDatabaseNameHere] WITH NO_LOG

GO

Fifth, record the logical file name for the transaction log to use in the next step.

USE [YourDatabaseNameHere]

GO

SELECT NameFROM sysfilesWHERE name LIKE '%LOG%'

GO

Sixth, to free the unused space in your transaction log and return the space back to the operating system, shrink the transaction log file.

USE [YourDatabaseNameHere]

GO

DBCC SHRINKFILE ([FileNameFromPreviousStep], [NeededFileSize])

GO

Seven, review the database transaction log size to verify it has been reduced.

USE [YourDatabaseNameHere]

GO

SELECT * FROM sysfilesWHERE name LIKE '%LOG%'

GO

Next Steps

Review your key SQL Server databases to determine if the transaction log growth is out of control.

Review this code and modify it for one of your databases.

Once the scripts are modified, test the scripts in a test environment to ensure they meet your needs.

Schedule time to shrink your databases and communicate the configuration changes.
Continue to monitor the database sizes and the available disk space on your servers.