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