Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to use a free utility called 'Parkdale' to easily test the performance and benchmark any Hard Drive(s) installed in your computer. We also look at RAM Disks and their speed comparisons.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

581 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