March 20, 2009 at 9:41 am
I am trying to convince one of our managers for more frequent transact log backups during the day or at night (currently I have a job that does it once per night), also to give him in simple words why SHRINKING database log as a scheduled task and frequently without log backup ios not a good procedure. Please give me any advise, hint or point me to articles that would back me up? He keeps telling me "if we haev 3 full backups run through the night, so why can't we just shrink the log in the morning as schedule?"
Gail, you usually give very sound advice as well as show articles on why log shrinking is not the best practice, i have alrady marked your article on SSC for him, can you give some incentives please,
Thank you
March 20, 2009 at 1:33 pm
EllieDBA (3/20/2009)
I am trying to convince one of our managers for more frequent transact log backups during the day or at night (currently I have a job that does it once per night), also to give him in simple words why SHRINKING database log as a scheduled task and frequently without log backup ios not a good procedure. Please give me any advise, hint or point me to articles that would back me up? He keeps telling me "if we haev 3 full backups run through the night, so why can't we just shrink the log in the morning as schedule?"Gail, you usually give very sound advice as well as show articles on why log shrinking is not the best practice, i have alrady marked your article on SSC for him, can you give some incentives please,
Thank you
Ellie,
Dpending on how transactional the Database is, full backups might do it. However, I am a DBA that errs on the side of caution. The real question that should be asked is, "How much data are you willing to lose?"
Most of the Databases that I deal with are in use 24/7, with much higher utilzation during core business hours. With that being the case I take one Full backup daily and Transaction Log backups every hour. All of the files are written local to the Database Server, then a batch process makes a copy to another Server and then my enterprise backup system copies the backp files to tape. I have notifications on each step to ensure that the backups are completing. The e-mail group receives a notification on both success and failure, with another process watching for failures that sends out an SMS alert.
As long as I can get to one of my backup locations, the most I stand to loose is 1 hour of data. Right now we are gearing up for more frequent backups, but only on a select few databases so that we can be within 15 minutes of the failure.
Secondary to all of this, is shrinking operations on a Database files, either Data or Log.
Generally speaking your data file will grow at a static rate, with a few exceptions, and should continue growing at that approxmate rate. If the growth statistics are set to high, then there will be alot of freespace within the file. Not necessarily a bad thing, unless of course your limited on disk space. It is my practice to watch file growth and adjust accordingly, but never to shrink the file unless there is a space constraint.
The Log file on the other hand should remain fairly static, providing regular Transaction Log Backups are being run. Constant shrink and regrowth of the file increases disk fragmentation which in turn will cause performance of the Server to suffer. Basically if your Transaction Log grows to about 1 GB in a time frame between Transaction Log Backups, it should not get too much larger unless there is an unplanned event occurring causing a hightend level of transactions. Once the Transaction Log file attains a static size it should not be shrunk, unless it has grown out of control.
You, as the DBA, must take the time to complete your due dilligence. In essence if you are reviewing the Database on a regular basis the file should not be growing to fast.
Regards, Irish
March 20, 2009 at 1:48 pm
Jeffery's pretty much got it.
The interval between log backups is the maximum amount of data that you are willing to lose in a disaster. If the allowable data loss for a DB is 10 min, the log backups must be no less frequent than 10 min (and more frequent is good)
If restore to last full backup is acceptable, then set the DB into Simple recovery and leave the log alone.
Repeated shrinks and grows of a transaction log can result in file-system fragmentation and lots and lots of Virtual Log Files within the log. That slows down backup operations and, possibly, normal operations of the DB as well.
Have a read through this and all linked articles - http://www.sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-III-Transaction-Log-Maintenance.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2009 at 2:01 pm
We run transaction log backups every 15 minutes, 25x7, on all transactional databases. It lets us restore to as close in time as possible after a problem, and it keeps the transaction log files from getting too big.
They also lets you recover after an accident, like accidentally deleting data or dropping a table. You can restore a copy of a database to another location up to a point in time just before problem to get all your data back.
There is no real down side to doing the backups that often. They don’t put much of a load on the system, certainly less than running full backups 3 times per day, and they probably use a lot less disk space. We run a full backup once per day, as a general rule.
Shrinking the transaction log often can cause severe internal fragmentation of the log file, and lead to very poor performance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply