Database BackUp Duration Increased Substantially ? 1

[edsuk]

Hi

Would appreciate any help offered on the following:

We have a production database set to Full Recovery Mode, with transaction log backups, differential backups and a daily full backup.

The daily full backup (which backs up to a separate server) normally takes around 4 minutes to complete.

Yesterday the backup was stopped after it had been running for over 90 minutes. No error logs have been raised, and therefore I cannot understand what caused this to happen ?

Ocassionally someone may still be using the Db whilst the backup is running, but again in the past this has not caused any problems with the backup itself.

However, yesterday when someone was still using the Db, the front end application timed out. i.e. stored procedures did not complete.

Once again, any advice would be very much appreciated.

Thanks

Mark...

 

[mkal]

I wouldn't backup over the network to a separate server. My experience has shown me this is fraught with problems i.e backups that don't complete or are corrupt. Your better plan is to backup locally and then copy the backup off-server.

You can also run a sp_who2 the next time this happens to see if some other process is blocking the backup.

And from BOL:

SQL Server uses an online backup process to allow a database backup while the database is still in use. The following list includes operations that cannot run during a database or transaction log backup:

File management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options; INSERT, UPDATE, or DELETE statements are allowed during a backup operation.


Shrink database or shrink file. This includes autoshrink operations.
If a backup is started when one of these operations is in progress, the backup ends. If a backup is running and one of these operations is attempted, the operation fails.

Cross-platform backup operations, even between different processor types, can be performed as long as the collation of the database is supported by the operating system. For more information, see SQL Server Collation Fundamentals.

mk