June 21, 2007 at 9:50 am
Hello,
I have an hourly job set up in SQL Server 2000, that backs up the Transaction Logs for two databases. The larger database is Step 1 of the job and takes apx. 9 minutes to run. The smaller database is Step 2 of the job and takes 25 minutes to run. I'm not a DBA, but know enough SQL to develop/support my apps. What kinds of things should I be looking for, as to why the smaller database's log file takes longer to back up? I've compared how the two databases are set up, and they appear to be the same. The only difference being space allocation. The job steps are also identical, except for the names of the log files and back up files. Both backup files are in the same directory.
Database / Step 1:
Size:1619.69 MB
Space Available: 600.03 MB
Data File (Space Allocated): 1225 MB
Transaction Log (Space Allocated): 396 MB
------------------------------------------
Database / Step 2:
Size: 134.50 MB
Space Available: 75.40 MB
Data File (Space Allocated): 66 MB
Transaction Log (Space Allocated): 70 MB
-------------------------------------------
All the above Data and Log files are set to automatically grow by 10%
Both db's are set as "Full Recovery" Model
Both db's have "Auto update statistics" and "Autocreate statistics" Settings
Both db's are set at "Database compatibility level 80"
Permissions are the same for both db's
---------------------------------------------
Any help would be appreciated. I usually put new releases of an app I support, up during our lunch hour. However, when database changes are involved, my window of opportunity is down to half an hour because of the hourly trans log backups. If I can reduce the time it takes to backup the tran log for the smaller database, I'll have more time to make database changes.
Thanks,
Tony
June 21, 2007 at 11:40 pm
Is it configured through a maintenance plan job or you use your TSQL code. If the second option then perform a checkpoint before you start the back log operation. 9 minutes for just 400MB itself seems to be very long time. canyou specify the hardware that yo use.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 22, 2007 at 5:07 pm
If you are backing up to the same drive as where your data files reside, then you are creating an IO bottleneck by forcing the disks to read and write at the same time. Try backing up your databases to a drive seperate from your data files.
June 25, 2007 at 4:25 am
for these files the backup times are extremely long. Either your hardware is maxed out or the backups are being blocked (check using sp_who2 during the backups), though few things block a backup (another backup, dbcc jobs, optimisations).
even though the second database has smaller files it may have more logged activity to back up. Check this from the size of the .trn files produced by the backup.
you can use 'select * from sysprocesses where spid in (your backup connections)' to see what the waittypes are and how long plus run system monitor concentrating on disk io to se what is going on, where the hold ups are ,during the back ups.
---------------------------------------------------------------------
June 25, 2007 at 7:46 am
Hi,
Try running the second log backup first (or by itself) and see if that makes a difference.
June 26, 2007 at 2:23 pm
"... even though the second database has smaller files it may have more logged activity to back up. Check this from the size of the .trn files produced by the backup...."
DITTO
June 27, 2007 at 2:06 am
But he has given the details of the files from which we can see that log file is very less in size than the first one. The problem is with the disk i hope disk i/o is causing the problem.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 28, 2007 at 2:36 pm
I agree with the poster that suggested changing the time of the job. If you do that and all stays the same, you know it's a problem with that database. If it is quicker, then you know it's the time that it was running at.
-SQLBill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply