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
