Verification slow on SQL servers
Two of our jobs take four to five times as long to verify as they do to backup. The agents are running on the remote servers. All other backup jobs appear to verify quicker than the backup.
Example1: SQL server one with 140GB of data took 16 hours for full backup 12 of which went to verifying the data drive. The backup of the same location took 2 hours 40 minutes.
Example2: File and print server backup of 280GB took 3 hours 1 of which was the verify of the data drive.
All the backups are to disk. Both examples are remote servers running the remote agent. The media sets are different. The hardware is HP same model servers and software with the except of SQL.
The SQL backups originally took around 2-3hours then all of sudden they started the slow verification.
Why is the SQL server verification taking so long?
- Hi Christopher,
A Verify option will check what's on tape compared to what's on disk, and if there are any inconsistencies, it would show these up.
SQL has 3 manners of doing a consistency check after the backup completes (taken from the Help file):
Select a consistency check to run after a backup. Because database transactions can occur during or after the consistency check, but before the backup runs, consider running a consistency check after the backup to ensure the data was consistent at the time of the backup.
The following options are available:
-
None. Select this option if you do not want a consistency check to run after a backup. Symantec strongly recommends that you always run a consistency check either before or after the backup. This option is selected by default.
-
Full check, excluding indexes. Select this option to exclude indexes from the consistency check. If indexes are not checked, the consistency check runs significantly faster but is not as thorough. Only the data pages and clustered index pages for each user table are included in the consistency check. The consistency of the nonclustered index pages is not checked.
-
Full check, including indexes. Select this option to include indexes in the consistency check. Any errors are logged.
-
Physical check only. Select this option to perform a low overhead check of the physical consistency of the database. This option only checks the integrity of the physical structure of the page and record headers, and the consistency between the pages' object ID and index ID and the allocation structures.
The option Physical check only is not supported under SQL 7.0.
Check to see if you have any of those options set. A Verify is generally going to take almost as long as your backup, but Symantec recommend it to ensure there are no issues with the DB after backing it up.
Let us know what the settings are...
Laters! -
