Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/247692

From BetaArchive Wiki
Knowledge Base


BUG: Backups Over Network Using INIT Take Longer to Complete When Dumping to Devices Already Containing Backups

Article ID: 247692

Article Last Modified on 10/16/2002



APPLIES TO

  • Microsoft SQL Server 7.0 Standard Edition



This article was previously published under Q247692

BUG #: 57072 (SQLBUG_70)

SYMPTOMS

Backups take longer to complete when the following conditions are true:

  • The database is backed up to a network dump device.
  • The backup is NOT appended to the network dump device.
  • The backup is performed using SKIP, INIT, FORMAT or any combination thereof.

as opposed to when:

  • The database is backed up to a network dump device.
  • The backup is NOT appended to the network dump device.
  • The backup device is first deleted from either Windows NT or by using xp_cmdshell.

In other words, when backing up databases over the network to the same dump device and not appending the data, backups take longer to complete when using INIT, FORMAT, SKIP or any combination thereof than when the backup device is first deleted using Windows NT or xp_cmdshell.

The following shows a comparison of time taken to backup different sized databases when the backup devices do not exist versus when they do:

Name of DB/Size Time taken to backup when backup device files DO NOT EXIST Time taken to backup when backup device files DO EXIST
OneMB 0.743 seconds (1.412 MB/sec). 0.976 seconds (1.075 MB/sec).
TenMB 5.635 seconds (1.861 MB/sec). 8.641 seconds (1.213 MB/sec).
HundredMB 47.279 seconds (2.217 MB/sec). 73.090 seconds (1.434 MB/sec).
ThousandMB 471.571 seconds (2.210 MB/sec). 694.533 seconds (1.500 MB/sec).


WORKAROUND

Add an extra step to the backup job to delete the remote backup disk device using xp_cmdshell.

Please refer to SQL Server Books Online for more information on xp_cmdshell and on setting up jobs. The following topics may be useful:

SQL Server Books Online Topic URL
xp_cmdshell (T-SQL) mk:@MSITStore:C:\MSSQL7\BOOKS\SQLBOL.CHM::/html/xp_aa-sz.htm
EXECUTE (T-SQL) mk:@MSITStore:C:\MSSQL7\BOOKS\SQLBOL.CHM::/html/ea-ez_2.htm
Defining Jobs mk:@MSITStore:C:\MSSQL7\BOOKS\SQLBOL.CHM::/html/automate_7.htm



NOTE: The preceding URLs assume that you have SQL Server Books Online installed in the following directory:

C:\MSSQL7\BOOKS


STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.


Keywords: kbbug kbpending KB247692