December 6, 2004 at 3:43 pm
Hi all,
I have a backup which seems to have slowed down for a while but seems to have come good now. The only thing I could pick was that the pages processed for the log file went from 20 to 500+...I wouldnt have thought that would have made a differece though. The percentage used space in the log or data file has not fluctuated all that much. The database is in simple recovery mode. Below is the log extracted from sql agent showing the output when the job took under an hour to process, and the output when the job took over three hours.
Any ideas would be much appreciated...Regards, Terry
----------------------------------------------------------------------
Executed as user: service. Processed 4519368 pages for database 'finprod',
file 'finprod_Data' on file 3.
Processed 20 pages for database 'finprod', file 'finprod_Log' on file 3.
BACKUP DATABASE successfully processed 4519388 pages in 3114.803 seconds (11.886 MB/sec).
The step succeeded.
-----------------------------------------------------------------------
Executed as user: service. Processed 4524328 pages for database 'finprod',
file 'finprod_Data' on file 3.
Processed 566 pages for database 'finprod', file 'finprod_Log' on file 3.
BACKUP DATABASE successfully processed 4524894 pages in 11363.465 seconds (3.262 MB/sec).
The step succeeded.
December 6, 2004 at 8:35 pm
Backups are I/O intensive. Perhaps some other process was tying up the disks where the database and backups reside e.g. virus scanning apps performing a scheduled scan, another process moving some large files around etc?
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
December 7, 2004 at 10:17 am
Also, are you initializing a new file? Might be a disk filling issue, though I doubt it.
I'd check to see that something else hasn't been scheduled to run at the same time and is affecting this.
December 7, 2004 at 4:54 pm
Are you bashing your disk by backing up to the same drive/disks/array that have the database files on it?
It seems to me that there is definately something else happening on your disks. Your first backup shows a transfer rate of 11.886 MB/sec and your second shows a 3.262 MB/sec rate.
Using Windows Performance Monitor, check out your physical disk queues, reads and writes per sec. Watch it for a few minutes before performing a backup, so you can see what it is like idle. If you see one disk increases both reading and writing during the backup, then that will be your bottle neck.
For highest native file backup performance, you should backup to completely separate physical disks from the disks that store the database's .mdf / .ldf files.
Another solution I highly recommend is Peter Yeoh's MiniSQLBackup http://www.yohz.com/product_msb.html which will compress your backups, so it will take up less disk space, and increase the backup speed.
Julian Kuiters
juliankuiters.id.au
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply