Competition: What’s your favorite Redgate tool? Enter now.

Transaction Log Backups slow

jpassarellijpassarelli Posts: 7
edited February 28, 2008 11:19AM in SQL Backup Previous Versions
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 [SAM4] TO DISK = ''\\DBSrvr4\Backup\<AUTO>.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?

Comments

  • 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.
    Peter Kristolaitis
    Database Administrator
    VON Canada
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
  • peteypetey Posts: 2,358 New member
    I apologize for the confusion. Forgot to put on my 'user' hat when responding.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.