July 25, 2008 at 8:52 am
I am rather confused by this, my transaction log backups jumped from taking 2 minutes to complete to 17 minutes.
I haven't changed anything with the database, no new automated processes, bulk imports, etc...
(I do have a bulk insert that happens daily, but that database is set to simple recovery only so I don't do log backups on that one)
Any suggestions on what I should start looking at?
I backup straight to tape using MS SQL 2005 Management studio built in tools.
Granted, I suppose the problem could be with my tape drive... so I'll be sure to look at that, but what else should I do to start troubleshooting this problem in SQL Server?
Thank you.
July 25, 2008 at 11:27 am
can you check to see if it's the actuall backup of data taking a long time or the truncation of the log? my log backups are supposed to take a few minutes and sometimes take longer because it has to work though truncating the log at the right place
July 25, 2008 at 12:40 pm
How can I do that?
(I'm a developer who happens to be the DBA, so my detailed DBA skills are somewhat lacking. I can write great TSQL, but when it comes to maintenance routines... well 🙂 I'm going to study up through the Technical Specialist exam from Microsoft on SQL DBA, but it looks like I'm going to need those skills sooner than later with this problem I'm having 🙂 )
Is that detailed out somewhere in the history logs of that job (my tlog backup job)?
July 25, 2008 at 12:48 pm
not sure
i use evault for backups and it comes out in the log how long each step takes. check the app log. should have separate entries for each step of the backup
July 27, 2008 at 4:55 am
check the size of ur transaction log. also check the size of the logical drive. If it is almost full, then it may take time.
July 28, 2008 at 7:59 am
Well this doesn't make any sense.
As the databases are not being used for data entry as much as reporting the logs are very small.
The entire database server's set of logs being backed up is ~ 130MB.
This should happen VERY quickly... yet it is taking 17 to 20 minutes to backup to tape... I'm wondering if perhaps there is a tape issue I should be examining?
I have one database with a very large log file, but that database's log is NOT being backed up as it is in simple recovery mode...
Suggestions?
July 28, 2008 at 8:29 am
I tried doing a restore headeronly from the tape drive witn NOUNLOAD thinking maybe the tape drive was taking forever to respond or something, but that process only took 1 minute and 25 seconds roughly both times I executed that command.
(Granted that might be a VERY long time, but for a tape drive I just assumed that is "normal")
So I'm not really sure where to look at next.
July 28, 2008 at 8:46 am
See here: topic
When you append backups to a file or device, each time the backup runs it checks each of the devices previous backups. So, the more backups you append, the longer the process takes.
Now, toss in the direct-to-tape backup and I can see your times escelating quickly as the tape drive has to check each physical appended backup on the device (tape takes 10x+ longer than direct disk).
Edit:
I've never been a fan of direct to tape backups. If the tape is bad (which is quite common, and why you should have a rigorous tape restore verification process), you lose your only copy of the backup. Using a disk and staging it to tape leaves you at least the one copy on disk if your tape is bad.
Your friendly High-Tech Janitor... 🙂
July 28, 2008 at 9:01 am
Good point.
It sounds like I need to adjust my backups to backup to the hard disk first and THEN move them to tape via NT Backup or something?
July 28, 2008 at 3:16 pm
IMO, yes, you should be using some type of more reliable staging media and find a way of automating that to tape. I would also suggest that you either abandon the idea of the appended backups to one device/file and instead move to a 1:1 device/file for t-log backups. Or at least look into a way to minimize the number of appended backups - which probably is more work than just going to 1:1 backups.
Your friendly High-Tech Janitor... 🙂
July 28, 2008 at 6:35 pm
Can you elaborate on the 1:1 instead of appending? I guess I thought you HAD to append T-log backups... apparently I am sorely mistaken...
Are you saying I would just backup the log, then make inactive, and make a new log backup file?
July 28, 2008 at 6:58 pm
Exactamudno!
An example that backs up t-logs every hour would go as follows:
At 2pm a job would run.
BACKUP LOG FooDB
TO DISK = 'C:\BACKUP\FooDB_20080728_1400.TRN' -- 2PM log backup
WITH INIT
GO
At 3pm the same job runs, it just assigns a different timestamp value to a new file.
BACKUP LOG FooDB
TO DISK = 'C:\BACKUP\FooDB_20080728_1500.TRN' -- 3PM log backup
WITH INIT
GO
What I've shown is the end result of code that is built dynamically (Dynamic SQL) and uses a variable to assign a timestamp value to the file. If you want to assign variables to certain pieces of a BACKUP command, you'll end up using dynamic sql to do it. Some portions of the BACKUP commands can handle a variable inline in it's call, some can't.
There is some complexity to setting it up but it's not too bad. You also now have the overhead of managing a bunch of files instead of just a singular device.
Your friendly High-Tech Janitor... 🙂
July 28, 2008 at 9:11 pm
With files you can create new ones without issue. With tape I think you are always appending or overwriting the entire tape, not something you want to do.
July 29, 2008 at 7:02 am
OK, that makes more sense.
With the tape that is correct. I have it overwrite when it does the FULL backup of everything then it appends. Tapes change, it keeps appending until that night when the full backup is performed and it is overwritten. Then the process starts over again. Granted this means that when I do my point in time restores I have to grab last night's tape, restore from the full backup, then restore up to say 8 AM when the tapes were changed. Grab the next tape and finish restoring until say... 11:30 AM when the issue occured that caused my need to restore.
As for the backing up to many t-log backup files (or rather to one seperate and unique file every time my t-logs backup) I would have to load up each file individually in my t-log restore process.
So if I had 10 of them since the last full backup, I'd have to pull all 10 in chronological order up from the oldest to the most recent.
Is that correct?
So if I adjust my backup plan to be something more like:
Full backup - backup to one of the local hard drives on the SQL Server.
--Once complete I write some TSQL or CMD or something to execute as the last step in the maintenence plan to copy it or move it onto the network somewhere.
--Then I have something pick that up and COPY to tape. Probably also good to move those same files into a sub folder called "Backed Up To Tape" or something so I "know" those have been backed up to tape and to keep the staging area clear for the next set of backups
--I also have "something" that deletes out old backups, maybe more than 3 days old, maybe a week, maybe a month depending on how much storage space I have on the network to hold them there.
T-Log backups (every 30 minutes at 00 and 30 of the hour)
--Same as above basically
For both these backup routines I'd have a datetime stamp added to the backup file name to keep all the files seperate and to let me know when they were created so I can restore in the proper order.
Something like that sound about right?
July 29, 2008 at 7:30 am
Spot on.
FWIW, restoring t-logs from files is really not much different than from a device. You can create a loop to iterate the directory and basically build your restore statements for you. Very close to how you would build a loop to iterate the device and build your restore statements.
Your friendly High-Tech Janitor... 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply