Automatic Tlog generation time mismatch??

Question

Automatic Tlog generation time mismatch??

dilip-196624

Ten Centuries

Points: 1270
More actions
June 28, 2005 at 3:17 am

#167310

Greetings!
I have written a restore script which is scheduled in a job..The problem is sometimes the tlog backups which i have scheduled to take place in 5 mins interval, slightly the timings get altered and this effects my restore program..e.g.
_200506280250.._200506280255..this sequence goes on and suddenly i see the next tlog takes place at 3:01 i.e. delayed by 1 min..y this happens??..This is very rare..my auto program is running since last week but this is the second time im experiencing this problem in last 2 days..there is no other job running at the same time..then why this match?? Anyone experiencing the same problem or any solutions?
Regards,
Dilip

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15266 More actions · Answer 1

I wouldn't count on the naming to be consistent using this method. If you need the names to be consistent and predictable, you will need to come up with a naming convention and implement it via a scripted backup routine rather than allowing SQL Server to automatically name the backups.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

dilip-196624 Ten Centuries Points: 1270 More actions · Answer 2

I dont think this is a naming convention problem, becoz I checked the backupset table for that particular slot and the backup_Start_finishdate it shows the same time i.e. 3:01pm...Any more ideas?

DCPeterson SSCoach Points: 15266 More actions · Answer 3

I guess I didn't make my point clearly. No it isn't a naming convention problem per se, but the SQL Server scheduler makes no guarantee about the exact time events will be started. It normally kicks things off right on time, but particularly with things like backups the start and end times can be affected by other processes. So relying on the timestamp tacked on at the end of the backup file name isn't going to be a reliable or at least a predictable naming convention. If you need predicability, you need to control the naming of the files.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

dilip-196624 Ten Centuries Points: 1270 More actions · Answer 4

Thanks for the inputs..But I have used another technique so that I get reliable timestamp everytime i.e. i use a linker server to connect to my source database and get the timestamp info from msdb..backupset table and accordingly pull the file on that info. this way i think i no need to depend on filenames solely. i have a 2 way check now..