VERIFY delay...

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

VERIFY delay...

Postby SQL_ME_RICH » Fri Jul 20, 2012 6:06 pm

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
SQL_ME_RICH
 
Posts: 64
Joined: Tue May 08, 2012 10:41 pm

Postby ChrisAVWood » Fri Jul 20, 2012 7:27 pm

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
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby petey » Sat Jul 21, 2012 4:58 am

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 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm

Postby toad2898 » Fri Aug 03, 2012 11:10 am

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.
toad2898
 
Posts: 27
Joined: Mon Sep 14, 2009 2:36 pm

Postby petey » Fri Aug 03, 2012 12:03 pm

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 7
petey
 
Posts: 2226
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests