Transaction Log grows even with backup

[tmurnion]

We have several databases on SQL 2k in Full recovery mode. We do a complete backup and one transaction log backup every day for each database through Database Maintenance. I thought the transaction log backup would truncate the log but it is not happening on our server. The transaction logs keep on growing.

Any thoughts?

Thanks in advance

Tony

 

[tmurnion]

I did a manual transaction log backup through Enterprise Manager and it did truncate the log. Why wouldn't the Database Maintenance backup also do the same. I don't see any settings for truncate in the DB Maintenance like there is in the regular backup.

Thanks.

Tony

 

[SQLRickster]

When SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log. This inactive portion contains completed transactions and so is no longer used during the recovery process. Conversely, the active portion of the transaction log contains transactions that are still running and have not yet completed. SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space.

Although the transaction log may be truncated manually, it is strongly recommended that you do not do this, as it breaks the log backup chain. Until a full database backup is created, the database is not protected from media failure. Use manual log truncation only in very special circumstances, and create a full database backup as soon as practical.

It is my guess, and this is purely a guess, is that SQL Server is seeing some transactions as being not yet completed. This can be seen when a BEGIN TRANS statement(s) has not been committed or rolled back. One could use SQL Profiler or Query Analyizer executing "sp_who2 'active'".

One suggestion. We have had issues with using the database maintenance wizards in the past. We therefore use a T-SQL statement in a job to 1.preform a DBCCC 2.Backup. Works great.

Good Luck
SQLRickster....