Options

Log Backups Taking 2-3hrs to complete and should take minutes

I have a case where my log backups using SQLBackup are taking 2-3hrs to complete and they should take less than minute according the data in backupset.

In backupset the largest difference between backup_start_date and backup_end_date for log backups is 31 seconds, but each time the log backup job runs it is taking 2-3hrs to complete. This seems to have become an issue when we changed from backing up to a local drive to a network share (the COPYTO was the same). The largest log backup in the last week is 675MB uncompressed.  In backupset I see a gap of 7-8minutes between backup end date and start data of the next backup and this is true of full and diff backups as well.  I don't see the same issue with native backups, which are only system databases, but still not gap between one ending and the next starting.

Does SQLBackup have any known issues backing up to a network share with encryption and compression?  
Could it be the COPYTO?

Here is the command in the job step:

<div>DECLARE @exitcode int</div><div>DECLARE @sqlerrorcode int</div><div>EXECUTE master..sqlbackup '-SQL "BACKUP LOGS EXCLUDE [master,model,msdb,DbMaint] TO DISK = ''\\ServerName\SQLBackup\<DATABASE>\<AUTO>.sqb'' WITH ERASEFILES = 7, ERASEFILES_REMOTE = 8, PASSWORD = ''<OBFUSCATEDPASSWORD></OBFUSCATEDPASSWORD>'', CHECKSUM, COPYTO = ''\\servername\backup01\proddb'', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 1, COMPRESSION = 4, KEYSIZE = 256, THREADCOUNT = 2"', @exitcode OUT, @sqlerrorcode OUT</div><div>IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)</div><div>BEGIN</div><div>RAISERROR ('SQL Backup failed with exit code: %d&nbsp; SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)</div><div>END
</div>
Jack Corbett
http://wiseman-wiseguy.blogspot.com
SQL Server Architect\Developer
MCITP: Database Administration SQL Server 2008
Tagged:

Answers

Sign In or Register to comment.