Backups taking extreme amount of time
timnjoan33
Posts: 4
Up until about a month ago, our nightly backups of a 215gb db were taking around 2.5 to 3 hours. Since then, they have taken anywhere from 8 to 18 hours.
I have tried backing up locally and to a network share and the times remain from 8-18 hours. I have also moved the backup times around to make sure it's not colliding with another process on the server.
I checked with our netadmin people and there have been no hardware changes to the server or nic.
I'm using version 3.0.0.3.
Any ideas?
I have tried backing up locally and to a network share and the times remain from 8-18 hours. I have also moved the backup times around to make sure it's not colliding with another process on the server.
I checked with our netadmin people and there have been no hardware changes to the server or nic.
I'm using version 3.0.0.3.
Any ideas?
Comments
It's not a free upgrade, but you'd get a new 14 day trial if you wanted to check out how much faster your backups are.
SQL Backup Project Manager
Red Gate Software
We can measure the throughput of SQL Server reading the data from disk by running a backup using SQL Backup with the NOCOMPRESSWRITE option e.g. (note that I'm using the command line interface as I will then be able to cancel the process)
sqlbackupc -sql "BACKUP DATABASE pubs TO DISK = 'e:\temp\pubs.sqb' WITH INIT, NOCOMPRESSWRITE"
We can then measure the throughput when compression is added, with the NOWRITE option e.g
sqlbackupc -sql "BACKUP DATABASE pubs TO DISK = 'e:\temp\pubs.sqb' WITH INIT, NOWRITE"
In your case, if you have a smaller database that is also experiencing a slowdown in backup speed, it might be easier to test using that database instead of the 245 GB one.
You need not wait for the backups to complete to get a measure of the throughput. SQL Server provides a performance counter (SQLServer:Backup Device) that will monitor the throughput in real-time. Note that you can only select an instance when the backup is running. By using the command line interface, you can stop the backup anytime if you feel you've obtained enough measurements.
If testing the NOCOMPRESSWRITE option on a single processor/core machine, you might need to set the backup to run at a low priority, otherwise Performance Monitor might not be able to display the performance counter for selection. To run at a low priority, use the THREADPRIORITY option e.g.
sqlbackupc -sql "BACKUP DATABASE pubs TO DISK = 'e:\temp\pubs.sqb' WITH INIT, NOCOMPRESSWRITE, THREADPRIORITY = 2"
VERY IMPORTANT NOTE: If you allow the backups to complete using the NOCOMPRESSWRITE or NOWRITE options, the backup is assumed to be made by SQL Server. If you made a full backup, the next differential backup will be made based on changes to this 'virtual' full backup. Always make a physical backup after using the NOCOMPRESSWRITE or NOWRITE options. This does not apply to version 4.5 and above.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8