SQL backup slowdown from 12 mins to 2 hours Server 2003

[tooluka]

Hi,

we are having problems with a server Intel RZeon 3ghz, 3gb ram running 2003 service pack 2 with a 70gb drive and and 400 gb drive all with adequate free space. There are 6 hard disks in total and i assume operating at least RAID 5. We have SQL2000 server with a few standard sized databases and a connection to one other server.

A few months ago the back up of SQL server databases started taking 4- 5 hours when before it took 20 minutes. We had actually lost one of our disks in the RAID array and it before this was spotted by our engineers we reindexed the sql databases and defragged both 70gbC:\ and D:\ 400gb drives hoping to correct this slow down. Unfortunately the new disk had not been correctly seated and this was why it was taking 4-5 hours. After fixing the disk the backups took 12 minutes again but then started taking 2-3 hours after a few days.

The reindex/defrag did seem to improve the speed of the backups to 12 minutes (from 20 minutes) when the backup did function correctly (also the sql databases' performance improved). However the backups only take 12 minutes after a server reboot - this can last from only 2, up to 5 backups(days) in a row before a slow down to 2-3 hours and again only a reboot will sort out this problem.

NB this intermittent slowdown only occurred after the disk failure.

We have tried monitoring SQL server and can find no CPU/RAM intensive clashes or long running jobs interferring with the back up. Does anyone know what might be going on here? and if there are any server monitoring tools that may help us discover what is causing this problem ?

thanks for your help

best regards

kevin

[cluberti]

Not sure about specifics there, but I'd start here:

http://msdn2.microsoft.com/en-us/library/ms190954.aspx

http://msdn2.microsoft.com/en-us/library/a...60(SQL.80).aspx

Also, running process monitor from sysinternals while the backup runs should give you an idea of what is being written to disk by whom, and how long the writes are taking. Perfmon SQL counters are a good tool as well.

[tooluka]

thanks for your reply.

although optimising server performance is important it doesn't explain the massive increase in time when the job runs 12 min from 2hrs after a server reboot i.e. it is performing for sometime relatively well so why a sudden change and why does a reboot solve the issue?

We have already looked at the microsoft monitoring tools and they look quite complicated and it looks like we would need training to interpret some of the output it's generating.

Here is some extra info that might help:

1: the back up is approximately 12GB. The size doesn't fluctuate that much in fact it would slowly be increasing. They are not all the same size they range from a couple of meg to 3.5GB. All the databases are backed up at the same time and we are measuring the total time for all to be backed up in one job.

2: the 400gb holds the backup, databases, and transaction logs, the 70gb only program files. I agree that the transaction logs should be on a separate physical drive and the backups on a separate server (we keep a backup on the same drive to restore quickly and if there is a huge catastrophe we restore from tape a full server image backup which is physically removed everyday). Although this is not ideal perfomance and safety wise we believe that it still does not explain why it can perform a 12 minute backup and then the next day a 2 hour backup of pretty much the same thing and then a reboot will take it back to a 12 minute backup time

3: We are using in enterprise manager through database maintenance plan -> backup all databases

4: Users are regularly feedingback that the databases are running faster than normal during the day at the time the backup is taking only 12 minutes at night. So it seems the performance is affected througjout by this issue.

5: The databases should only be being used during the day.

6: We are current with Windows service pack upgrades but under the advice of MS support my colleague said that we should only upgrade SQL service packs if we have a known issue that will specifically be fixed by an upgrade.

Do you have any suggestions surrounding why a reboot might actually improve SQL performance?

thanks

best regards

kevin

[cluberti]

It could be a slow resource depletion issue, a filter driver issue, or a few other kernel-specific things that I can think of that would all clear up with a reboot.

1. Are you running realtime antivirus on this server? If so, uninstall the product and reboot to see if that changes the behavior.

2. Does your tape backup software use an Open File Agent component? If so, uninstall and reboot as per the previous suggestion.

3. Look in your system event logs for any 2012, 2019, 2020, 2021, or 2022 events - if so, some reg changes might need to be made to tune the box.

[tooluka]

Hi,

it turns out that the new disk that was added had a different firmware to the other disks - a firmware upgrade for all disks and a RAID controller upgrade seems to have done the trick

thanks to all who tried to help

best regards

kevin