May 2, 2016 at 10:54 am
I have a SQL 2012 Server. Our data is stored on a Dell SAN. All of it is connected using 10g TP. I have 22 individual databases (clients) with the same structures but varying amounts of data.
For the last month, our backups have taken approx. 24 hours to do a full backup of all databases. Last Friday, the facility where the hardware is at experienced a catastrophic failure of their power systems and we were down for 5 hours. After everything was restored, I ran checkdb against all databases and no issues.
Every Sunday at 2am we start a backup job that sequentially does a full backup for each database. As stated, this was a 24+ hr process. This last Sunday, it took approx. 1.5 hours.
Any ideas why the difference? Why does it slow down? What can I monitor?
Any help would be greatly appreciated.
Thanks,
Mike
May 2, 2016 at 11:33 am
If your SAN is a Compellent - and you are backing up to the same file, then it could be that file is getting dropped to the lowest tier on the SAN. Which may be set to 7500K drives...
If this is the case, then you need to change your process so you create new files for every backup (date stamped). This will force the SAN to use the highest (write) tier which normally will be SSD drives. The files will be dropped to the lowest tier over time but that only matters if you are using the files for a restore.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 2, 2016 at 11:41 am
Nope, our SAN is a the other one. We don't have SSD.
Mike
May 3, 2016 at 7:36 am
Hi,
First of all ensure that all backup files are correctly formed (means with no corruption). You can do this by checking backups with Restore with VERFILYONLY option atleast for few important ones.
If backups are correctly formed, then you can look the Transfer rate in the History of the SQL Agent job and compare it with transfer rate of old execution stats. If it is higher this time it means SAN was not working fine and problem started after reboot.
Also check for Network issues with help of network managing team, which might incurred during the time when backup job run.
Regards,
Ankur
May 3, 2016 at 10:04 am
mike 57299 (5/2/2016)
I have a SQL 2012 Server. Our data is stored on a Dell SAN. All of it is connected using 10g TP. I have 22 individual databases (clients) with the same structures but varying amounts of data.For the last month, our backups have taken approx. 24 hours to do a full backup of all databases. Last Friday, the facility where the hardware is at experienced a catastrophic failure of their power systems and we were down for 5 hours. After everything was restored, I ran checkdb against all databases and no issues.
Every Sunday at 2am we start a backup job that sequentially does a full backup for each database. As stated, this was a 24+ hr process. This last Sunday, it took approx. 1.5 hours.
Any ideas why the difference? Why does it slow down? What can I monitor?
Any help would be greatly appreciated.
Thanks,
Mike
I have found that using compression with multiple file paths on backup provide the best performance in getting backups written as quickly as possible. Usually no more than 8 files per backup but that is for our 100GB+ databases.
BACKUP DATABASE [databasename] to
disk=C:\backup\databasename_full_backup01.bak,
disk=C:\backup\databasename_full_backup02.bak,
disk=C:\backup\databasename_full_backup03.bak,
disk=C:\backup\databasename_full_backup04.bak
WITH COMPRESSION,INIT , NOUNLOAD , NAME = 'databasename backup', NOSKIP , STATS = 10, NOFORMAT
As with any new feature or setting you are best testing out what will work best for your system/network.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply