Drastically Slow or delayed or extended backup

[Former Member]

Dear fellow Basis members,

My 45B system is using SQL Server 7 and there is online full database SQL backup with logs daily in late night.

It used to finish within 4-5 hours (eg. by 3am). Nowadays, it could stretch to complete only by lunch time!

During monthend, there are more batch jobs running. But how could this drastically and directly have attributed to the much longer backup time?

The Transaction logs have increased by about 2-3 times but that only means about 10+GB more. I could not understand how this 10GB could extended the backup duration. There is apparently no serioud system problem during backup.

I'm looking into whether its due to network, tape drive related problem. By the way, last night's backup was good as it could complete within the window.

Could someone give some advise. Thanks!

Regards,

Pascal Gabin

[Former Member · Accepted solution]

Pascal:

Are you backing up over the network? are you using standard SQL backup agent or another backup application like Veritas ?

Network problem could be affecting your backup times.

Best Regards,

Federico G. Babelis

http://www.gazum.com

[Former Member · Accepted solution]

Dear Federico,

Yes, we're using SQL backup. Today, the backup seems to have been triggered but no progress as the logs showed nothing. In fact the log was not yet generated. There is an error log which says DBCC Traceon then trace off .

Could disk IO cause backup to be unable to even start writing if there is heavy batch jobs running throughout the night and into the day? That is, can IO 'waits' cause the backup to permanently wait on-hold? The tape drive is blinking slowly but may not have data written as no log is written. But if io waits (caused by sequential reads?) can cause this, why the system still can process other database requests?

Could there be some settings set on the SQL database or agent that cause this? The backup tape device is a locally scsi-connected tape drive to the machine. But the throughput has been reducing from 15 mb/s to 3 mb/s.

This has been happening for days and I could not pinpoint the fault. So, I hope Basis experts here can help me out. Else, I may be in trouble.

There is no apparent system or database problem that I can see.

Best regards,

Pascal Gabin

[Former Member · Accepted solution]

Hi Pascal:

OK, did you upgrade the tape device driver or have you changed/applied any hotfix or service pack before this start happening ?

I don't think in slow disk access time; otherwise your entire application will face the slow performance problem.

How big is your database ?

I saw pretty similar issues and we solved by doing a full database re-indexing (with the help of a maintenance plan) after that procedure performance and database times were increased considerably.

Best Regards,

Federico G. Babelis

http://www.gazum.com