I would appreciate your comments on this issue. I want to switch off the "verify backup integrity" toggle in the maintenance plan (MP from here on in) as I am consistently getting an error of the following nature every week when I run the main backup process:
The error is:
"VERIFY DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
The step that are occuring in the Maintenance Plan are:
- Check database integrity task
- Update statistics task (but see below)
- Backup database integrity task
- If current backup runs successfully then remove the previous backup
Concerning #2 - we found that this was taking prohibitively long so I isolated that task and identified the long running indexes.
I now update the statistics the the night before the backup process and take a suitable sample for example:
UPDATE STATISTICS [dbo].[TBL_LONG_RUNNING] WITH SAMPLE 50 PERCENT
Anyway because of the error mentioned above the previous backup is not removed. Despite the fact that the new backup is successfully created. I get emailed with an alert and its now annoying me.
Hence, I want to switch off the "verify backup integrity" task and get some sounding on doing just that. For example, I read somewhere that to be sure the backup should be restored periodically and a DBCC CHECKDB run to make sure it is sound.
Therefore if you could make any suggestions/comments/advice I would greatly appreciate it.
Thanks in advance, J.