Error Handeling and DBCC CHECKDB

A client that I’ve been working at for a while needed to start doing DBCC CHECKDBs on their production server.  We hadn’t been for a little while due to maintenance window constraints on their old production server.  Now that we’ve scaled the application across 6 different servers we can now do DBCC CHECKDB on the production server regularly to ensure that there aren’t any database corruption problems on their production server.

As a part of this configuration we wanted to do the DBCC CHECKDB as a part of the backup job so that after the DBCC CHECKDB is complete the databases will then be backed up.

The catch here is that we didn’t want to have to dig through the errorlog file to figure out what the database was that’s having the problem, and we didn’t want the job to fail if there was a problem so I started looking at catching errors when running DBCC CHECKDB.  Sadly there aren’t really any good ways to do this.  TRY/CATCH doesn’t work because DBCC doesn’t actually throw error messages like a normal SQL statement does.  It returns the errors, but it doesn’t actually throw the errors so the CATCH block isn’t actually captured.  Running DBCC CHECKDB within an EXEC sp_executesql doesn’t catch the error either for the same reason, the error isn’t thrown it is simply displayed.  (The reason that the errors from DBCC CHECKDB show up in Management Studio in red I would assume is because SSMS is catching the format and displaying it correctly.)

To catch the errors I had to resort to some old school “error handeling” using the @@ERROR system function.  While this isn’t perfect, I’m not looking for perfect here.  I’m just looking for something that says that there’s an error so that I can send an email when there is an error then continue to loop through the databases looking for others with problems.

As this is step 1 of a multistep job this step is configured to move on to the next step on success or failure (as an email will have been sent and the data logged to the ERRORLOG) then we’ll procede to the backups.

The code that I’m using looks something like this…

/*Populate the table #dbs with the databases that need to be checked.*/
DECLARE cur CURSOR FOR SELECT name from #dbs
OPEN cur
FETCH NEXT FROM cur INTO @name
WHILE @@FETCH_STATUS 0
BEGIN
SET @sql = 'DBCC CHECKDB ([' + @name + '])'
EXEC sp_executesql @sql
IF @@ERROR 0
BEGIN
set @subject = 'CHECKDB failure for ' + @name
set @body = 'DBCC CHECKDB failed for database ' + @name + '
Command run was: ' + @sql
exec msdb.dbo.sp_send_dbmail...
END
FETCH NEXT FROM cur INTO @name
END
CLOSE cur
DEALLOCATE cur

There’s some custom code at the top which figures out which databases to process so that the job step runs DBCC CHECKDB on the same databases which the job will backup (this is figured out based on the database size, if the database is online, and if the database is currently being bulk loaded).  This code isn’t shown as it’s not relivant to this specific problem.

Denny

Share

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trust DCAC with your data

Your data systems may be treading water today, but are they prepared for the next phase of your business growth?