Why Am I Getting A Large Transaction File Backup?

[jbraund]

We recently upgraded our SQL server from 7.0 to 2000. I have a maintenance plan set up that is suppose to perform a transaction log backup from 7:00 AM to 9:00 PM every two hours for a single database. Currently the 7 AM transaction is failing and then the 9 AM transaction is around 5.6 gigs. This is consistent. There is nothing that runs overnight that could accumulate this large of a tran file. The other tran files that run throughout the day range from 150 mb to 10 mb. The actual log file is 5.7 gigs. Did the log file get mangled from the upgrade? Does it need to be shrunk or recreated? Does this even have to do with the upgrade? So why is the 7 AM tran failing and the 9 AM so large?

 

[Lezza]

When the log dumps, is the log file cleaned out? Or does it still have 5.6 gb of data within the file? If it does, then the log isn't dumping properly. Check for an open transaction on the system which may be holding 5.6 gb of the transaction log open. If the log is cleared out each time you do a dump, then there's a process that is running which is causing the log to fill. You can run SQL Profiler during this time period to determine what is happening on the transaction side. Another question I would have is why is the transaction dump failing?

 

[SQLBill]

Are you doing your transaction log backups to separate backup files on disk?

-SQLBill

 

[jbraund]

Lezza

Now the 7AM tran file backup is a bit over 6 gigs. It doesn't seem to be failing anymore. After it runs the log file doesn't shrink. I think I read something about transactions remaining open. I will search again, if you have any good references let me know. Thanks.

------------------------------------------------------------

SQLBill

The database and log files are on the E drive, and the actual backups taking place are on the F drive. Thanks.