Slow copying?

audun

Hi,

I have a script that runs through all our databases and backs them up.
This looks like:

SET @BackupFile = @dbname + '_full_' + @time_stamp+ '.zbak'
SET @complete_filepath = @filepath_backup + @BackupFile
SET @command = '-SQL "BACKUP DATABASE ' +@dbname + ' TO DISK = ''' +@complete_filepath
+''' WITH NAME = ''' + @BackupFile
+''', DESCRIPTION = '''+ @Description
+''', COPYTO = '''+@filepath_compressed_backup+'compressed_full_backup'
+''', KEYSIZE = 256'
+', MAILTO_ONERROR = '''+@mailto_onerror
+''', PASSWORD = ''xxxxxxx'', INIT"'
exec master..sqlbackup @command

This script works as expected, except for one thing: it sometimes runs slow.

In the logs a backup is reported to take e.g. 9 secs, but the next database in the loop is not backed up for several minutes.

How does sqlbackup works when it comes to the copying?
Will it wait until the copying is done before it continues?

The log says:

SQL Backup log file
1/16/2007 7:03:01 AM: Backing up p127748301 (differential database) on INSTANCES2 instance to:
d:\backup\p127748301_diff_20070116_070301.zbak

1/16/2007 7:03:01 AM: BACKUP DATABASE [p127748301] TO DISK = 'd:\backup\p127748301_diff_20070116_070301.zbak' WITH NAME = 'p127748301_diff_20070116_070301.zbak', DESCRIPTION = 'Diff. BACKUP at 20070116_070301.', DIFFERENTIAL, INIT, PASSWORD = 'XXXXXXXXXX', KEYSIZE = 256, MAILTO_ONERROR = 'someone@somewhere.com', COPYTO = '\\192.168.100.179\d$\backup\sql\confirm_dbserver\compressed_diff_backup', COMPRESSION = 1

1/16/2007 7:03:11 AM: Backup data size : 135.313 MB
1/16/2007 7:03:11 AM: Compressed data size: 36.668 MB
1/16/2007 7:03:11 AM: Compression rate : 72.90%

Processed 17232 pages for database 'p127748301', file 'p127748301_dat' on file 1.
Processed 1 pages for database 'p127748301', file 'p127748301_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 17233 pages in 9.021 seconds (15.648 MB/sec).
1/16/2007 7:03:15 AM: Copied d:\backup\p127748301_diff_20070116_070301.zbak to \\192.168.100.179\d$\backup\sql\confirm_dbserver\compressed_diff_backup\p127748301_diff_20070116_070301.zbak.
1/16/2007 7:03:15 AM: SQL Backup process ended.

I am unsure whether the copying is done by the time sqlbackup reports: SQL Backup process ended

Anyone?

Regards

petey

The 'SQL Backup process ended. ' message is displayed after the copy has completed.

Have you set up the option to 'Delete SQL Server backup history'? This uses the sp_delete_backuphistory stored procedure, which can be inefficient (at least in SQL Server 2000) if your backup history has a lot of entries.

Try removing that option, and see if the interval between backups is reduced. If it does, you could either periodically run that stored procedure, or turn the option on in SQL Backup and consider some suggestions to improve the performance e.g. http://forums.databasejournal.com/archive/index.php/t-19068.html. Do note that is a system table, so you might want to consider the pros and cons of such an action.

audun

Hi,

the "Delete SQL Server backup history" is not turned on.

Any other ideas why this might be?

Regards,

audun

Hi,

I probably found the reason.

For some reason the automatic deletion of the sqlbackup logs did not work.
There were thousands of log files in each instance folder.
I hard deleted these and the diff backup speeded up again.

Regards,

petey

Does the SQL Backup service user have rights to read and delete files in those folder? It needs to be able to list the files, open each read to look for the SQL Backup tag, and delete them.