Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Managing SQL Server 2000 Transaction Log Growth


By:   |   Read Comments (1)   |   Related Tips: 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.


Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, July 29, 2009 - 3:14:59 PM - Credo Back To Top

 Hello,

 

I just went through the process of shrinking the database transaction log file.  It worked like a charm - Thank you VERY much.

 

I know it's been a while, but I have a few small questions:

1) how should I schedule transaction logs to be backed up - my database is accessed/updated VERY frequetnly, so I was thinking about backing up the log about every 30 - 45 minutes.

2) How do I keep the transaction log on the SQL server small like it is now?  Does backing up the transaction log more frequently keep sizes small?  Should I restrict the file growth of the transaction log to a specific size in the database properties?

 The main reason my log file is growing so quickly is because I'm trying to run a DBCC REINDEX on my largest table, which has never been done.  How can I run that reindex if it keeps growing my log file to insane sizes???

 

Hopefully someone's still around to read, and help me with this situation.  If so - thanks in advance!


Learn more about SQL Server tools