How to Find Out Why Your SQL Server 2005 Transaction Log is Growing

If you ever notice that your transaction log is growing in SQL Server 2005 (either the % space used is increasing or the file itself needs to be grown, then you have a problem that you need to investigate. First, if you are running a database with a Full Recovery model, you need to regularly back-up and truncate the Transaction Log, or else it will fill up and begin to grow the log file. Most DBA’s know this, but I often see people asking about it in the MSDN Forums.

If you are backing up and truncating your log, and it is still growing, then you need to find out what the problem is ASAP! You can run the query below to see the log_reuse_wait_desc for each database on a SQL Server instance. Common reasons you may see are Log_Backup, Active_Transaction, and Replication. This tells you what the problem is (in general terms).

-- Find the problem with the transaction log
    SELECT name, database_id, log_reuse_wait_desc 
    FROM sys.databases

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s