April 24, 2009 at 4:39 am
Hey All,
I have a quick (multi-part question) which hopefully is not that hard to get to the bottom of. Articles I have read have been conflicting and I wanted to put it to a vote.
If a database has a full recovery log file which is backed up multiple times a day what happens if:
1. The log grows to the extent whare it cant be backed up in the windows between backs (the log is backed up every 15 minutes but takes 20 to backup). When will the next log backup be? 5 minutes before the end of the first (two jobs clash) or 15 minutes after the first job completes?
2. If a log is 50GB but only has 10% usage (90%) free, will the backup only be the data portion (around 5GB)?
3. In the case that the database is mirrored what are the results of 1 & 2 on the mirrored copies?
Know some of this may seem obvious, but if you could be a sanity check for me that would really be appreciated.
As a seperate issue, I have a databases (set to simple), the log has been limited in growth to 2GB but grew to 8GB. It was empty, but why did it grow so high?
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 24, 2009 at 6:27 am
Schadenfreude-Mei (4/24/2009)
1. The log grows to the extent whare it cant be backed up in the windows between backs (the log is backed up every 15 minutes but takes 20 to backup). When will the next log backup be? 5 minutes before the end of the first (two jobs clash) or 15 minutes after the first job completes?
Neither.
10 min after the first job completes. If a job is still running when it's next scheduled start time comes, it won't be started (a job can't be running twice) and SQL will schedule the next run of the job according to the required schedule.
So basically if your job is scheduled every 15 min, but each execution takes 20 min, the job will run once every 30 minutes.
2. If a log is 50GB but only has 10% usage (90%) free, will the backup only be the data portion (around 5GB)?
Yup. Backups don't back up empty space
3. In the case that the database is mirrored what are the results of 1 & 2 on the mirrored copies?
Not sure, not enough experience mirroring.
As a seperate issue, I have a databases (set to simple), the log has been limited in growth to 2GB but grew to 8GB. It was empty, but why did it grow so high?
Simple recovery doesn't mean the log won't grow. You must have had some large single transaction that resulted in 8 GB of log space been used (index rebuild or reorg perhaps) or there was a long running transaction that prevented the truncation of the log. Once the next checkpoint ran, the log would be purged, but not shrunk.
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
April 24, 2009 at 9:23 am
Mirroring shouldn't be affected by log backups. If mirroring pauses, however, then it can affect the log. It can't be cleared until those entries are moved to the mirror.
April 28, 2009 at 6:14 am
Gail, Steve,
Thanks for the speedy response. Sorry I took a while to respond, had what the wife calls a 'man-cold', though i'm sure its 'bird flu'.
Anyhoo, thanks for the response, pretty much what i thought but wanted to check.
Steve, when a successful backup on the live log completes, does it update the mirror with the checkpoint? We are thinking of putting in a log shrink after the backup, but i wanted to check that this was also going to be replicated to the mirror.
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 28, 2009 at 6:55 am
Schadenfreude-Mei (4/28/2009)
We are thinking of putting in a log shrink after the backup, but i wanted to check that this was also going to be replicated to the mirror.
Why, why, why do you want to shrink the log? It's very likely going to grow again, the shrink uses resources, the grow uses resources (possibly at the worst possible time), a log can't use instant initialisation so it does take time, and repeated shrink-grow cycles can cause file-level fragmentation.
The usual advice for a log is to size it for the max that is required for the usual workload and then leave it alone.
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
April 28, 2009 at 4:42 pm
Schadenfreude-Mei (4/28/2009)
We are thinking of putting in a log shrink after the backup, but i wanted to check that this was also going to be replicated to the mirror.
Yes, it will be replicated to the mirror but as Gail clearly stated shrinking the log is a bad idea.
April 29, 2009 at 3:24 am
GilaMonster (4/28/2009)
Why, why, why do you want to shrink the log? It's very likely going to grow again, the shrink uses resources, the grow uses resources (possibly at the worst possible time), a log can't use instant initialisation so it does take time, and repeated shrink-grow cycles can cause file-level fragmentation.The usual advice for a log is to size it for the max that is required for the usual workload and then leave it alone.
Hey Gail, thanks for your response
As you may recall we have had many chats about this sort of thing in the past and while this is not something i would do in a production environment, this is for a VMWARE dev environment where the server only has 100GB of log space (which is being used).
Therefore this is an attempt to 'control' its growth even though we know we will get a performance hit. By backing up the log regularly and putting in a shrink job to truncate if it reaches 50GB we can at least ensure it doesnt suck all the drive space and cripple the system.
While i would normally NEVER go against your advise, this seems to be the only option (extending the drive or adding more disk space is not an option as it would have to be duplicated ove 8 environments).
Thanks for your help 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 29, 2009 at 7:05 am
Schadenfreude-Mei (4/29/2009)
As you may recall we have had many chats about this sort of thing in the past and while this is not something i would do in a production environment, this is for a VMWARE dev environment where the server only has 100GB of log space (which is being used).
Ah, makes sense.
I assumed prod because it's not very common to see a mirrored dev system, or a dev db that's legitimately in full recovery.
I chat about this with so many people I can never remember who's doing what, where and why.
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
April 29, 2009 at 7:15 am
You mean you cant remember all 11,098 responses off-hand? Tut-tut I'm disappointed?;-)
Adam Zacks-------------------------------------------Be Nice, Or Leave
April 29, 2009 at 7:15 am
DUPLICATE POST. Please ignore. content removed.
Adam Zacks-------------------------------------------Be Nice, Or Leave
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply