Daylight Savings Time and Backups
-
Forgive me if this is sort of a noob question
, but I've asked around a lot and have never gotten a satisfactory answer to this question. We have several production servers that are set up to dump the transaction logs hourly on all user databases. In the spring when time moves up an hour, this is no problem and one hourly dump just gets skipped when the clock moves ahead, but an unbroken chain of successive log dumps is still there allowing a restore.In the fall when the time moves back an hour is where the problem comes in. Say that it's 2:00 am and my hourly dumps all kick off, then the server gets automatically time synched at 2:15 making it now 1:15 am again. Then 2:00 am hits and what happens with the log dumps? I believe that the Agent already sees a 2:00 dump and nothing happens until 3:00 am where I get a backup containing all transactions from (the new) 1:15 until 3:00 am. We have another Sybase DBA here who says that no, at 2:00 am the dump will overwrite previous one thus breaking the chain of log dumps. I find that hard to believe especially since I've been unable to locate any documentation on what acutally happens in this scenario during a DST conversion. I would think that if he were correct, I should find dozens of articles saying 'Warning: Don't let your backups get overwritten during DST!' or something similar. Keep in mind that these are primarily 9 to 5 business hours apps and I'm therefore not so much concerned about the actual database transactions (as there should be very few to none), but rather the log dumps themselves. Any info/insight is appreciated. Thanks.
My hovercraft is full of eels.
-
I would tend to agree with the Sybase DBA. The Agent is basically a scheduler, so it will probably just run the job again. This is definately worth doing some testing on, though.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
I just spoke to my colleague about this situation. She says that jobs which are scheduled for a specific time will be ok, but jobs that use a "frequency" type schedule, like every 1 hour, or every 15 minutes will have a problem.
I will definitely be "tweaking" the schedules on our servers, and I might throw a test job or two in there to see what would happen if left alone.
Steve
-
Thanks for the replies. To be safe, we've always been on site in the fall and just shut the agents down while the servers time synched, then brought them up again but I thought there was a way around this. Our jobs are hourly so looks like I'll be here late again. Guess I'll be sleeping late on Haloween morning
In the meantime if I can find a dev server and take it off the network, I'll try to figure out what happens with the agents when I force a time change. Thanks again.
My hovercraft is full of eels.
-
I certainly don't plan to be on site for the time change. I don't even stay up on New Year's Eve! I don't have a firm plan yet, but it will be something along the lines of setting an end date on the current schedule for 10/30, so that the log dumps stop at 11:59pm 10/30. Then create a new schedule that will run from 2:15am until 11:59pm on the 31st. Finally a third schedule starting on 11/1 that sets everything back to normal.
Another possibility... add a job that checks getdate(). If getdate() >= "2004/10/31 01:45:00 am", then execute sp_update_job to disable the transaction log backup job. Then if getdate() >= '2004/10/31 02:15:00 am" execute sp_update_job to re-enable the transaction log backup job. The job would run every 15 minutes or less only on 10/31.
Any thoughts?
Steve
-
sswords,
I'm curious as to why you run hourly log backups 24 hours a day if your database is being used "9 to 5".
Greg
Greg
-
Greg,
I said 'primarily' 9 to 5. We do have apps and instances where users are in the office after hours or can dail in and perform updates, but I would estimate that 95% of the activity is during what's considered 'normal business hours'. And it was sort of a path of least resistance when we originally set the servers up a while ago...you know, back then it just was easier for us to set up and track hourly dumps for the user DB's than to fool with the schedules (which I am looking at doing now in an effort not to be on site that morning).
I'm supposed to be getting some brand new servers in today that will have SQL Server installed and will be in development for a couple of months so I'll be able to experiment with these some. I am still quite curious to find out if a log dump will get overwritten in the scenario I described or if I will throw an error. Thanks to all for your comments. Happy Weekend everybody!
My hovercraft is full of eels.
-
I moved all our backups to use GMT time the backup and restore script compensate for DST that way I never have to worry about an overwritten file or that a backup will fail to restore.
Cheers,
Wes
-
For the record, sp_update_job is in the MSDB database, and the rule about stored procedures beginning with 'sp_' doesn't apply. My daylight saving time adjustment job was pointed at the Master database and failed on every server it was deployed on!!! The up side is that I learned that SQL 2000 handles the change even when the job is scheduled as "every 15 minutes". The down side is that I learned that SQL 7.0 doesn't handle it. Fortunately, I only have two SQL 7.0 servers, and one of them wasn't affected. However, the other one created two transaction log backups at "2:00". The first one ran for "-95444:-90:-51" but reported a successful backup. Its already been archived to tape, so I haven't tested it yet. I have a feeling it will probably be ok, as long as I get the sequence right.
Steve
-
If you subscribe to the SQL Server Magazine weekly e-mail newsletter there's an article this week that explains the SQL Server Agent behavior during the DST time change and this makes sense to me. Assuming the servers are set to automatically adjust for DST, the time will roll back one hour at 1:59.59.999, and the 'Next Run Date' was set for any scheduled jobs that have already run between 1:00 and 1:59:59 am. Therefore the Agent sees the Next run date for the jobs and essentially just sleeps between the new 1:00 until 2:00 am untill it comes across the next scheduled job to run. I'm assuming this applies to both 2000 and 7.0. Thanks to all for the replies.
My hovercraft is full of eels.
-
I don't think it applies to 7.0 . The one server I had that actually processed twice was a 7.0 server.
Steve
I do have the newsletter, but haven't looked at it. I will now!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply