February 7, 2023 at 9:29 am
Hi everyone,
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 occurring in the Maintenance Plan are:
1. Check database integrity task
2. Update statistics task (but see below)
3. Backup datbase integrity task
4. 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 the problem indexes, 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 a advance, J.
February 7, 2023 at 12:19 pm
First up, the only sure way to 100% validate a backup is good is to run a restore. The saying goes "You're only as good as your last restore." So, yeah, if you really want to validate your backups, the single best mechanism, is a restore.
That said, sounds like you have some kind of issue with the setup of your commands. If the same process and login is used to both run the backup and to run the integrity check (which is not a bad thing to do, it's just incomplete in terms of verifying the backup), I would think that they would both work. However, if you're running it as a separate process, or a different connection, or through a different user, that might explain the problem. Anything in the logs?
Personally, I like a belt & suspenders approach to backups. Run the backup with checksum so that the writes are validated as you go. Run the integrity check to ensure that the backup is internally consistent. Run a restore regularly (which is an undefined period of time that varies by database) to ensure that they're really good.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 9, 2023 at 12:11 pm
Hi Grant, thanks for your feedback. I think I'm going to move away from Maintenace Plans and use the relevant sql code as I will have more control over the various options. I suspect this checksum your referred to will be an option under that. I can also run the integrity check and see if the issue replicates...
T answer your query: no, nothing in the logs except the error already indicated. The process runs under the same user account for everything but the backup destination is going across a nas. That may have something to do with it...I'm not sure...
I think a full restore of the backup every quarter or perhaps every month and running a dbcc chckdb will be reassuring and put this issue to bed.
Thanks again. J.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply