MS SQL 2000 Backups, Maintenance Plans, Flushing Logs
Posted on 2010-01-11
I have a production ERP server that hosts a large MS SQL database 140GB.
My backup job schedule runs as follows:
Daily @ 13:00: Maintenance plan - Flat file transaction log file backup
Daily @ 19:00: Maintenance plan - Flat file full database file backup
Daily @ 21:00: Backupexec 11d - Full Database backup with remote agent
The daily transaction log backups for the ERP database are less than 1GB in size, yet the transaction log has somehow accumulated to 70GB in size. I ran a DBCC shrinkfile script on the database and transaction log file that brought the transaction log file down to 40GB, but I'm guessing that it should be much smaller.
Any ideas on what's keeping the log file so big, and why it's not flushing more out of the log with my current bakup strategy?
And any idea what I should do improve my backup strategy for this database?