DIFF backups getting slower over time

mbuis

We have a backup regime that does a FULL backup every night, a DIFF ever hour and a LOG every 5 minutes. Our database contains transactional data that changes every day, but the total amount of data stays pretty constant over time.

When we first set this up, the LOG backups were taking around 5 sec to run, now - two months later - they are taking around a minute. As the amount of data being backed up is the same, I don't understand where the slowdown would be coming from. (By comparison, the DIFF backups take 1-2 minutes).

Here's the backup command we're using:

master..sqlbackup -SQL "BACKUP LOG [Trading] TO DISK = [d:\redgatebackups\incrementals\Trading\Trading_LOG_2006.11.20_00.35.SQB ] WITH DESCRIPTION = [LOG backup of wopr Trading on Nov 20 2006 12:35AM] , NAME = [Trading LOG backup] , INIT, COMPRESSION = 1, ERASEFILES = 2

The files produced by the LOG backup vary a lot in size from 60KB to 116MB. The time taken varies from 50sec to 90sec, and is weakly correlated with the size of the file produced.

We've tried removing the log files produced by SQLBackup, but this has not affected performance. We're deleting the LOG files on a rolling 26 hour schedule, via another command line job.

Any clues about what could be causing this?

Brian Donahue

One possibility is the ERASEFILES. This option causes SQL Backup to examine all headers on all of the backup files in the folder, so it will delete the right ones. If more and more files are being retained in the backp location, then it's going to take longer and longer to examine and delete them.

Hope this helps!