VERIFY delay...

SQL_ME_RICHSQL_ME_RICH Posts: 112
edited August 3, 2012 7:03AM in SQL Backup Previous Versions
Hello -

Is there a switch to add for the VERIFY option, so that it doesn't wait 90+ minutes before starting the verification of the .sqb file? Or is the delay in part due to the CHECKSUM that I also have listed as an option for when my Fulls and Diffs kick off?

The database in question is about 265gb in size, and is being compressed. It will show in the activity log that the backup processed successfully, but then it take as long as 90 minutes before starting the VERIFY process. I am guessing that the size of the database is the issue, because I do not see this issue with my smaller databases.

Any insight would be appreciated.

SQL_ME_RICH
· Share on Twitter

Comments

  • ChrisAVWoodChrisAVWood Posts: 361
    You can include the VERIFYINTERVAL = number in seconds parameter as it was introduced in 6.5.2.9 but this would further delay the time your backup takes. I would expect that the CHECKSUM happens as part of the backup not the verify stage. Red Gate people will confirm this.

    If you have Page Verify set to CHECKSUM as a database option I would think the CHECKSUM in the backup could be redundant. True you would need to read every page in the database with Page Verify as CHECKSUM first to know that you had a good database before you run backups without CHECKSUM as all the newly writen pages are CHECKED because of the Page Verify setting.

    We have a DB that is about 170Gb of data that we compress with a setting of 2 to 2 files. The backup takes 45 minutes and the verify messages come out 30 minutes later as they only appear at the end of that process.

    If I remember correctly you try and compress this database with a level of 4 and haven't been able to complete a backup for at least a week unless that was fixed.

    Chris
    English DBA living in CANADA
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Could you please post the contents of the SQL Backup log file for that backup? . The default folder where the logs are stored is C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name> on Windows 2003 and older, and C:\ProgramData\Red Gate\SQL Backup\Log\<instance name> on Windows Vista and newer.

    Thank you.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • toad2898toad2898 Posts: 41
    Could I just ask if a workaround/fix/resolution, call it what you like, was given for this as I think i'm having the same issues since upgrading to 7.

    The reason I think this is because there are big delays between the time the backup is taken and the time it is copied to a network location.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Pls see my earlier reply - could you please post the contents of the SQL Backup log for the backup that had the verify process running long after the backup completed?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.