Options

Backups taking a looooooong time

JackieNubyJackieNuby Posts: 7 New member
in SQL Backup
I've noticed recently my backups are taking a very long time.  It seems to be related to the option to delete files older than 30 days.  If I uncheck that option, the diffs are done in 5 minutes.  If the option is checked, they're still running the next morning.  Any ideas?
Tagged:
· Share on Twitter

Best Answer

  • Options
    JackieNubyJackieNuby Posts: 7 New member
    edited April 20, 2020 1:16PM Answer ✓

    There ended up being an issue with inherited permissions on the database folders created for each database.  Once i turned on inherited permissions for each database folder, the file cleanup now works.
    · Share on Twitter

Answers

  • Options
    petey2petey2 Posts: 87 Silver 3
    Given that your retention period is 30 days, I'm going to assume this is on a network share.  The SQL Backup engine has to inspect each of the file on that network share to determine if it should be deleted, and would take a while if there are a lot of files on that folder.

    One way to reduce the amount of files is to split the backups by database name and backup type if you have not already done so.  So instead of storing all backups in the same folder e.g. TO DISK = [\\share\backups\...] or COPYTO = [\\share\backups\], use TO DISK = [\\share\backups\<instance>_<database>\<type>\...] or COPYTO = [\\share\backups\<instance>_<database>\<type>\].

    Another option, if space is not at a premium, would be to delete the old backups in a separate job, separate from the backup e.g.

    EXEC master..sqlbackup '-sql "ERASE FULL_BACKUPS FOR mydb FROM DISK = [\\share\backups\*.sqb] KEEP = 30"'
    See this page for the ERASE command details.
    SQL Backup - beyond compression
    · Share on Twitter
  • Options
    JackieNubyJackieNuby Posts: 7 New member
    edited April 7, 2020 2:33PM
    I ran that command.  I've attached image of output.  I still have backup files for DATA_01 from 1/16/2020.  Why doesn't it work?


    · Share on Twitter
  • Options
    petey2petey2 Posts: 87 Silver 3
    A few possibilities:

    - are the backup files password protected?
    - do the backup files belong to the same server/instance you are running the deletion from?
    - are the backups that were not deleted full database backups?
    SQL Backup - beyond compression
    · Share on Twitter
  • Options
    JackieNubyJackieNuby Posts: 7 New member
    - are the backup files password protected? - No
    - do the backup files belong to the same server/instance you are running the deletion from? - Yes, but I only have 1 SQL server, so how is that an issue?
    - are the backups that were not deleted full database backups? - No.  I still have full backups in the folder older than 30 days.
    · Share on Twitter
  • Options
    petey2petey2 Posts: 87 Silver 3
    When you use the ERASE command, it only deletes backup files:
    -  belonging to the same server/instance as the server/instance you are running the command from
    - for the specified database (DATA_01)
    - for the specified backup type (FULL_BACKUPS)
    - older than the retention period (30)

    If all these criteria match the backup files that were suppose to be deleted but were not, could you please post the results of running a RESTORE SQBHEADERONLY command for any one of the backups that should be have deleted but was not?  E.g.

    EXEC master..sqlbackup '-sql "RESTORE SQBHEADERONLY FROM DISK = [<filename>]"'

    You can send me the details at peter.yeoh@red-gate.com if you prefer not to post it here.
    SQL Backup - beyond compression
    · Share on Twitter
  • Options
    JackieNubyJackieNuby Posts: 7 New member
    Just a thought.  I've had issues with the spinning green circle too and i decided to clear the backup/restore history via this help article: https://documentation.red-gate.com/sbu9/troubleshooting/deleting-backup-and-restore-history-manually .  Could that be the issue?

    · Share on Twitter
  • Options
    JackieNubyJackieNuby Posts: 7 New member
    Just emailed the "RESTORE SQBHEADERONLY" output.
    · Share on Twitter
  • Options
    JackieNubyJackieNuby Posts: 7 New member
    I think i may have files that exist now that are no longer in the backup history.  Can i rebuild the backup history from the existing files?  Then i could run the ERASE command to remove the old backups?
    · Share on Twitter
  • Options
    David_jack123David_jack123 Posts: 5 New member
    edited December 14, 2020 12:46PM
    You may use the below query to track the progress of backup in SQL Server 2005 and above.
    then you should try to use the latest server.
    Thanks 
    · Share on Twitter
Sign In or Register to comment.