Long running backup job

Illuminations2kIlluminations2k Posts: 10 Bronze 2
edited October 19, 2006 11:19PM in SQL Backup Previous Versions
We are running SQB 4.6.0.815 and have noticed that our T-Log backups (which are scheduled for every 15 minutes) are taking 11-17 minutes every time.
In searching the forums I did find this post: http://red-gate.com/messageboard/viewto ... light=hung

I took the steps and have reduced the amount of data in the backup tables in MSDB; however, that has not reduced the time. We issued an UPDATE STATS and DBCC DBREINDEX on the tables as well to no avail.

SQB shows that the actual backup is only taking 1-5 seconds, what could possibly be taking so long with the actual job?

I did a DBCC INPUTBUFFER on the SPID and this is what is being run:

DECLARE @exitcode int DECLARE @sqlerrorcode int exec master..sqlbackup N'-SQL "BACKUP LOGS [XXXX] TO DISK = ''\\ctac\service\sqlbackup\RedGate\XXXX\XXXXX\<AUTO>'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', NO_TRUNCATE, ERASEFILES = 1, COM

Any help with this would be greatly appreciated!

Thanks!
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Try turning off all the auto-deletion stuff. In the GUI, on the Options dialog, select the 'Log Deletion' tab and uncheck both deletion options. Does the backup complete faster?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Illuminations2kIlluminations2k Posts: 10 Bronze 2
    I made the changes; however, the only item that was checked was 'Delete SQL Backup log files'. 'Delete SQL Server backup history' was not checked.
    I unchecked 'Delete SQL Backup log files' and it dropped the time from 12-15 minutes to 4 seconds. Thank you!

    Just out of curiosity, why was the deleting of the local log files taking such an extraordinary amount of time? It was set for 90 days and we've only been using the product for about 1/2 of that.

    Regardless, thank you for the help!
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Are there a lot of other files in the SQL Backup log folder? When deleting old log files, SQL Backup needs to open each 'file' to find the SQL Backup header, to ensure that it is a SQL Backup log file before deleting it. If you have a couple of thousand files in the folder, it will take a while for SQL Backup to work through the files.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Illuminations2kIlluminations2k Posts: 10 Bronze 2
    There were over 90,000 files in that folder so I can see why it might have taken that long!
    What is the purpose of these files? Is it important to keep them or is there a way to turn them off?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    They are the SQL Backup log files that contain the results of the backup/restore processes. They basically store the same output you see on screen, but won't see when you run it non-interactively e.g. via a scheduled task. It's used for troubleshooting errors etc.

    You can't turn them off. One suggestion is to turn off the automatic deletion, and manually delete/archive the old files periodically. Or use a shorter retention period.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Illuminations2kIlluminations2k Posts: 10 Bronze 2
    So, just to verify. We can delete the files without removing the ability to restore if necessary?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Just to verify: you are referring to log files that contain plain text results of SQL Backup processes, and not SQL Server transaction log files? If so, yes, you can delete those log files.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Illuminations2kIlluminations2k Posts: 10 Bronze 2
    Absolutely! :D

    I'm just referring to the plain text files, just wanted to verify that they are only for debugging purposes, not integral to the function of the application.
    · Share on Twitter
Sign In or Register to comment.