MS SQL 2000 Backups, Maintenance Plans, Flushing Logs

Posted on 2010-01-11
Medium Priority
Last Modified: 2013-12-01
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?
Question by:itfocus
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 75

Accepted Solution

Aneesh Retnakaran earned 1500 total points
ID: 26284981
>Daily @ 13:00: Maintenance plan - Flat file transaction log file backup
scheduke the TL backup more frequently, at lease once in an hour


Author Comment

ID: 26291899
As the transaction log is currently 40Gb in size, and when I already run 1 log backup per day, how will running multiple log backups every day help reduce the size of the actual transaction log?

Author Comment

ID: 26359702
As far I can tell, running multiple transaction logs a day won't actually decrease the size of a transaction log file - Only a DBCC Shrink will do this...

Author Closing Comment

ID: 31675564
As the solution did not actually resolve my issue of decreasing the size of the transaction log, but it did suggest a more efficient way of improving the actual backups.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question