Options
Slow transation log backups as compared to native....
swjohnson
Posts: 30
When I run native SQL Transaction log backups to a network share, my 250+ databases takes about on average 5 minutes to run. However when I use SQL Backup to process the same databases, it is taking about 20 minutes to run the transaction logs (compression level =1, no_truncate) to a local drive.
My machine is a dual P4 Xeon 2.6GHZ with 4MB RAM and tons of disk space and processor utilization is under 15%.
I have also noticed that if I use the CopyTo command to the network share, it takes about 10 minutes longer to run as compared to backing them up to a local drive and then running a batch file to move them to the network share. The batch files takes about 1 minute to transfer all the files.
Basically I have a script similar to the one provided in the help file that opens a cursor and loops through each of the DB's and executes the following statement.
SELECT @MyString= '-SQL "BACKUP LOG TO DISK = ''' + @BackupPath + '<AUTO>''
WITH NAME = ''Database (' + @dbName + '), ' + cast(getdate() as varchar(30)) + ''', DESCRIPTION = ''Full Backup on ' + cast(getdate() as varchar(30)) + ' Database: ' + @dbName + ' Instance: (local) Server: ' + @SvrName + ''',
PASSWORD = ''XXXXXXXXXXX'', NO_Truncate, VERIFY, ERASEFILES = ' + cast(@RetainDays as varchar(3)) + ', MAILTO_ONERROR = ''' + @NotifyAddr + ''', COMPRESSION = ' + cast(@CompressLvl as varchar(1)) + '" -E'
Any thoughts on how we can speed this up?
SJ
My machine is a dual P4 Xeon 2.6GHZ with 4MB RAM and tons of disk space and processor utilization is under 15%.
I have also noticed that if I use the CopyTo command to the network share, it takes about 10 minutes longer to run as compared to backing them up to a local drive and then running a batch file to move them to the network share. The batch files takes about 1 minute to transfer all the files.
Basically I have a script similar to the one provided in the help file that opens a cursor and loops through each of the DB's and executes the following statement.
SELECT @MyString= '-SQL "BACKUP LOG TO DISK = ''' + @BackupPath + '<AUTO>''
WITH NAME = ''Database (' + @dbName + '), ' + cast(getdate() as varchar(30)) + ''', DESCRIPTION = ''Full Backup on ' + cast(getdate() as varchar(30)) + ' Database: ' + @dbName + ' Instance: (local) Server: ' + @SvrName + ''',
PASSWORD = ''XXXXXXXXXXX'', NO_Truncate, VERIFY, ERASEFILES = ' + cast(@RetainDays as varchar(3)) + ', MAILTO_ONERROR = ''' + @NotifyAddr + ''', COMPRESSION = ' + cast(@CompressLvl as varchar(1)) + '" -E'
Any thoughts on how we can speed this up?
SJ
Comments
You can try splitting the backups into multiple threads by specifying a multiple file backup. This will potentially share out the workload to different processors on a multiple processor machine. If backups are taking a long time, it may be because the database contains a lot of image fields that already contain compressed data. This is the case in particular with SourceGear Vault databases, as I know from experience.
In this case, SQL Backup is effectively spinning its' wheels in trying to compress the uncompressable.
Hopefully this information is useful.
Re the difference in network transfer times, would it be possible for you to run a simple benchmark i.e.
1. start profiler
2. perform a single backup that takes a minute or so, using the COPYTO option to a network drive
3. time the entire process
4. note down the time in profiler when SQB disconnects. Any time remaining after that will be used for the network transfer.
5. compare this time against copying the same backup file using a regular copy command to the network folder
Thanks.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8