Transaction Log Backups slow

jpassarelli · February 27, 2008 6:57PM

I hadn't noticed it for a while, but sometime in the last month or so transaction log backups have become strangely slow. I know it use to take less than 30 seconds to do a backup and now it is over 3 minutes. I don't know for sure if this is an issue with SQL Backup or not, but I can't figure out where the slowdown is.

Here is the backup command I am running (ver 5.2.0.2824)

EXECUTE master..sqlbackup N'-SQL "BACKUP LOG &#91;SAM4&#93; TO DISK = ''\\DBSrvr4\Backup\&lt;AUTO&gt;.sqb'' WITH COMPRESSION = 2, THREADCOUNT = 3"'

The output:

Backing up SAM4 (transaction log) to:
\\DBSrvr4\Backup\LOG_(local)_SAM4_20080227_174337.sqb

Backup data size : 4.563 MB
Compressed data size: 342.500 KB
Compression rate : 92.67%

Processed 251 pages for database 'SAM4', file 'SAM_Log' on file 1.
BACKUP LOG successfully processed 251 pages in 0.146 seconds (14.051 MB/sec).
SQL Backup process ended.

That all looks great, but it actually took 2 minutes 51 seconds to complete. I thought it might have to do with a large backuphistory table in MSDB, but I purged the table and it is still slow.

Any suggestions on how to troubleshoot this?

pkristolaitis · February 27, 2008 7:32PM

Pop open perfmon while your transaction log backup is running and monitor disk stats (queue length, latency, bytes/sec) and network (bytes/sec, packets/sec) and see if either of them are pinning up.

petey · February 28, 2008 12:32AM

Best guess would be that you have set up SQL Backup to purge old entries in the msdb database, or old log files from the log folder, or both. Either of these could explain the slowdown, as they are both post-backup processes which do not show up in the timing, and are slow if there are many entries to process.

Try turning these options off, using the GUI, and see if the backup speed improves. Then identify which of these options is actually causing the slowdown.

If it is the deletion of old entries in the msdb database, you might need to manually delete some entries using the sp_deletebackuphistory stored procedure to keep the backup history tables to a manageable size. Or you could maintain a separate job that performs the deletion periodically, instead of having SQL Backup perform it after each backup.

If it is the deletion of old log files, you might want to consider performing the purge yourself. Alternatively, you could upgrade to version 5.3, as the deletion speed of outdated log files is greatly improved.

jpassarelli · February 28, 2008 10:10AM

It was the deletion of the application log files that was causing the slowdown. These files were in the folder C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\(LOCAL)Since we do transaction log backups every 5 minutes on two databases, and the file purge was set to 90 days, the application log folder had over 46,000 files. I manually purged the folder and set the option in SQL backup to only keep 2 days worth of logs.

Just an FYI, I was somewhat confused by the reference to "log files from log folder" reference in the above post. At first I thought log files referred to transaction logs. Even when I looked in the Options window in the GUI, I thought the reference to the log file folder was for transaction logs. After a few ..umm...minutes :oops:, I finally realized that the application was logging its activity to this folder. So my suggestion is to be a little more explicit when referring to a "log".

Thanks,
John

petey · February 28, 2008 11:19AM

I apologize for the confusion. Forgot to put on my 'user' hat when responding.

Transaction Log Backups slow

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk