December 28, 2011 at 10:12 pm
Comments posted to this topic are about the item Concurrent Operations
December 28, 2011 at 10:12 pm
Badda bing - log backups. People should know this one.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 28, 2011 at 10:53 pm
SQLRNNR (12/28/2011)
Badda bing - log backups. People should know this one.
True - but this was a change (in 2005) so for many people the change in behaviour will still be 'news'. An interesting detail is that the log backup doesn't clear the log when it finishes; that is delayed until the concurrent full or differential completes.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 28, 2011 at 11:14 pm
SQL Kiwi (12/28/2011)
SQLRNNR (12/28/2011)
Badda bing - log backups. People should know this one.True - but this was a change (in 2005) so for many people the change in behaviour will still be 'news'. An interesting detail is that the log backup doesn't clear the log when it finishes; that is delayed until the concurrent full or differential completes.
That is true - and unfortunate. It's been six years. Let's hope that this feature change becomes more mainstream knowledge;-)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 29, 2011 at 12:22 am
Good question Steve, remember reading about it in one of your blogs.
M&M
December 29, 2011 at 4:59 am
Good question.
Interesting change from 2000; I wonder why it was changed.
Tom
December 29, 2011 at 7:56 am
Probably so that it made scheduling log backups easier. If for some reason a full backup was taking longer than usual, a normal log backup could fail. This way you still get the normal log backup just without the log truncation. I noticed this by accident in 2005 and almost threw a party, since it made scheduling log backups every 15 minutes so much easier.
December 29, 2011 at 9:29 am
mohammed moinudheen (12/29/2011)
Good question Steve, remember reading about it in one of your blogs.
likewise... & tks Steve!
December 29, 2011 at 9:39 am
I am glad that I checked MSDN...
Thanks, Steve!
December 30, 2011 at 4:47 am
Could anyone please guide , as to, under which scenario should one opt for such concurrent activity ?
December 30, 2011 at 5:27 am
Good question, thanks Steve.
http://brittcluff.blogspot.com/
December 30, 2011 at 7:51 am
arun1_m1 (12/30/2011)
Could anyone please guide , as to, under which scenario should one opt for such concurrent activity ?
Suppose that your backups take a differing amount of time on any given day. Suppose also that you need point-in-time recovery to any point in a 24 hour period. Both scenarios are allowed under this type of concurrency.
First scenario -- You might get job failures if you are incorrect in predicting when the backup will finish if the two operations cannot co-exist. If you create a two hour window without log backups and the backup takes two hours twenty minutes, there will be at least one failure assuming a 15 minute log backup interval, but it isn't really a true failure. Believe me, that is a page at 2:30 in the morning you don't want to get.
Second scenario -- this isn't quite as important since you could always take a log backup after the full backup, but it makes it easier to do point in time recovery since you can take log backups that were taken before the full finished to recover to a point in time during the full backup. What this DOES help protect is if you have a disk failure DURING the backup. If your backup took three or four hours and you couldn't take log backups, there is a risk during that time of data loss that cannot be recovered if you lose disks before the full completes and you didn't get a log backup. Being able to continue log backups during the full backup gives an extra layer of protection.
December 30, 2011 at 9:58 am
jeff.mason (12/29/2011)
Probably so that it made scheduling log backups easier. If for some reason a full backup was taking longer than usual, a normal log backup could fail. This way you still get the normal log backup just without the log truncation. I noticed this by accident in 2005 and almost threw a party, since it made scheduling log backups every 15 minutes so much easier.
Yes, that does appear to be an advantage of allowing it.
But it was possible in SQL 2000 to get around it in other ways. For example part of a solution would be to have a backup job which did a log backup, and then did a full backup and another log backup if more than a certain amount of time had elapsed since the last full backup completed, otherwise it then did a differential backup and another log backup if more that some other amount of time had elapsed since the last full backup or differential backup completed, or did nothing but the log backup; of course if it did a full or differential backup it would record the date and time at which that backup completed; with provision for a flag to be set telling it to do a full backup next time it ran regardless of whether one was required by the timing rules, but not update the last full backup date/time that determined when further full backups would happen. Schedule it (using SQL Agent) to run every 15 minutes; if a log backup followed by a full backup and another log backup ever takes longer that 15 minutes, all that happens is that SQL Agent doesn't schedule the next backup until the next slot, because the single job is only allowed to be alive once at a time - there are no failures caused by attempting disallowed concurrency.
Of course this does not give you log backups that cover any period at which a differential or full backup is taking place but never completes; so there is a risk of losing more data if the system crashes, losing the current logfile, during a full or differential backup than you would lose in the same circumstances if you scheduled some log backups during differential or full backups, so the change introduces in 2005 allows for greater security (at the cost of increased backup space, and increased workload). I never thought of that particular issue when I was working with 2000, and didn't realise until now that the change fixed it, so your post has taught me something I might otherwise never have been aware of.
Tom
December 30, 2011 at 12:16 pm
I find the workload typically is easier since I can schedule tran logs without any reference to full backups. It would take longer to restore, maybe, to a certain point in time but I am not doing that on a regular basis and at that time my workload is the least of my concerns since my database has just failed more than likely if I am restoring that many backups. It doesn't require additional space for log backups, because the old way the log wasn't getting truncated and the first tran log backup after the full backup had the whole log file during the backup in it (unless you are duplicating the log backup each time you take it while the full happens, I don't know if that is happening but if file size is any indicator I kid of doubt that it is).
Another consideration I hadn't thought of until now as well is log shipping. If you can't take a log backup you can't log ship, so without a concurrent operation log shipping would be "down" (error free but out of date) during every full backup. That's probably not such a good thing either, y'know?
January 4, 2012 at 6:23 am
Is this question applicable for sql server 2005?
I mean, will it work concurrently in sql server 2005?
Regards
Skybvi
Regards
Sushant Kumar
MCTS,MCP
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply