By: Jeremy Kadlec | Comments (2) | Related: More > Database Administration
Problem
We have been running SQL Server 2000 for a few years and the transaction log file has become very large for some of our databases. In some circumstances, the transaction log is a more than 5 times larger than our database. How can I reduce the size of this file?
Solution
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 sysfiles
WHERE 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 Name
FROM sysfiles
WHERE 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 sysfiles
WHERE 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.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
