Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Gopher
Yak Posting Veteran
83 Posts |
 Posted - 2007-11-17 : 02:57:50
|
| Hi AllI am having a serious problem which I need some help with regarding our SQL Server backup.Basically it has started to take ages (as in 48hrs +), when it should only take about 4 hrs. The database is only 380GB and up until monday our backups have not been completing. When I check the activity monitor I have seen that the 'BACKUP DATABASE' process is set to suspended with a huge wait time and the wait type is ASYNC_IO_COMPLETION.I am not sure how to solve this, but I am going to have to!So if anyone has any ideas please help me! If you need any othe info please let me know.ThanksGopher |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-11-17 : 13:13:39
|
Has this gotten slower gradually over time or did you see a sudden spike in the time it takes? What are the details of your backup process, are you backing up locally or over the network? Do you have multiple backup devices? What is your recovery model? What type of raid array are you backing up to?
Future guru in the making. |
 |
|
|
Gopher
Yak Posting Veteran
83 Posts |
Posted - 2007-11-17 : 14:57:16
|
It will do my best to answer these:quote:
Has this gotten slower gradually over time or did you see a sudden spike in the time it takes?
It has suddenly started happening, Tuesday to me more specific, the backup starts ok then all of a sudden gets slower!quote:
What are the details of your backup process, are you backing up locally or over the network?
We are backing up from HP EVA storage to MSA storage - I know these is a speed difference with these two but it has never been this bad! Normally the backup will take 3hrs 50mins. It is across the network, but out of working hours.quote:
Do you have multiple backup devices?
No, one device in SQL that points to the MSA.quote:
What is your recovery model?
Full - Full backup every day and logs every hour between 7am and 7pm.quote:
What type of raid array are you backing up to?
RAID5 all of our storage is RAID5.As the SQL server that hosts the DB is an HP Proliant DL380 G4 I am wondering if it has anything to do with the smart start version? It could be a communication issue I am just not sure!If you need any other info let me knowThanksGopher |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-17 : 15:14:37
|
| Check SQLserver log and NT log for suspicious eventsAlso, may be your RAID5 is running on 4 disks? |
|
|
|
BigAl
Starting Member
2 Posts |
Posted - 2007-11-17 : 19:14:29
|
| Boot the server with a current SmartStart CD, if you do not have one download it from HP and run a full diagnostic of the hardware, the SmartStart version installed on the server shouldn’t matter. If you desire to update from the CD go for it but it won’t correct your issue. Perform a full diagnostic on the HP EVA / MSA also. This will check the integrated NICs, drives, controller etc and identify degraded hardware.Ask your network people to verify the throughput of the server and EVA / MSA NIC, throughput from NIC to MSA. Have them check the switches from errors. Also have them trap the throughput during the backup. They should have software to do all this.If you determined the source of the issue correct it. Regardless I do the following also.Download the HP Firmware CD and update the BIOS on all HP hardware. This at times does wonders. If you are using the HP NIC Team (dual NICs) update the drivers or reinstall them. Experience with these has found them to be the culprit more often then not when diagnosing throughput issues. You can not use the HP NIC Team also. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-17 : 21:28:04
|
| Checked load on MSA? |
|
|
|
Gopher
Yak Posting Veteran
83 Posts |
Posted - 2007-11-19 : 10:17:31
|
quote:
Originally posted by rmiao
Checked load on MSA?
The load on the MSA seems ok as we have many backups using this storage and they are having no issues. However I have found a few warnings:SQL Log:Date 17/11/2007 09:46:19Log SQL Agent (Current - 17/11/2007 09:46:00)Message[425] delay_between_response attribute (10 sec) of alert (9) is less than poll interval for this alert (20 sec)This appears a few times, this can be changed in the registry but I am not sure what difference it will make! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-19 : 22:53:51
|
| I'll check bytes write per second on MSA in perfmon in this case. |
|
|
|
|
|
|
|
|
SQL Backup taking too long!!!