1

I have setup this alert that I got from Brent Ozar's Website.

EXEC msdb.dbo.sp_add_alert 
  @name=N'Severity 016',
  @message_id=0,
  @severity=16,
  @enabled=1,
  @delay_between_responses=60,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000';
GO

I wanted to use it to be alerted when I run DBCC CheckDB and it finds a corrupt database but It does not send an alert. The Alert is working like it is supposed to. I get alerts for other level 16 events.

Here is the Message I get on the screen when I run CheckDB on a known corrupt database but I do not get an alert:

Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 885578193, index ID 0, partition ID 72057594042974208, alloc unit ID 72057594049396736 (type In-row data), page (1:8096). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 2, Line 1
Object ID 885578193, index ID 0, partition ID 72057594042974208, alloc unit ID 72057594049396736 (type In-row data): Page (1:8096) could not be processed.  See other errors for details.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'Corrupt_Tbl' (object ID 885578193).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'CorruptionTest'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptionTest).

How can I ensure I will get an alert when CheckDB finds corruption on errors like this?

3
  • Does the message appear in the windows event log? This is where Agent look. If it is not there, then agent don't know about it, and there's no chance for an alert. Aug 3, 2021 at 16:53
  • Yes the events are logged in windows events. However the ones that I get alerts and emails on have an error as their level but the corruption that is found with the corruption test is only logged as informational. It is logged as information, Event ID 8957, DBCC CHECKDB (CorruptionTest) WITH no_infomsgs executed by S-CED-GIS1\Administrator found 2 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. Internal database snapshot has split point LSN = 00000024:000002a3:0001 and first LSN = 00000024:000002a1:0001. (I though when the database found corruption it should alert)
    – BRJ5144
    Aug 4, 2021 at 15:46
  • Thanks for the update. I wrote an answer with some more info, to benefit future readers of this entry. Aug 5, 2021 at 11:53

2 Answers 2

1

Just add Agent Job with DBCC CHECKDB and enable alert there with mail notification. Additionally, it would be good if you'll write down to file the error logs for this job.

Erin Stellato has good post about configuring DBCC CHECKDB

0
1

Agent alerts are based on messages going to the Windows Event log. Agent reads the eventlog every 20 seconds and matches the new messages to the alerts you have configured. I.e., in order for an Agent alert to fire, we need to have a matching entry in the event log to the alerts we have created.

Say you run into a corruption while you(r application) does a SELECT on a table. SQL Server will write information to the event log so this can be caught by some monitoring solution (like an Event Alert). Probably a 833 or 834 error.

However, DBCC CHECKDB is a "deliberate" searching for corruptions. This idea is that you look for errors in the output from the command, and that is why you don't get alertable errors in the eventlog. One can argue about that design, but it is what it is.

End result is that you can't use Agent Alerts to catch problems that the DBCC CHECKDB command finds. You need to execute the command in such a way so that the outcome of that execution can act that you got an error from DBCC CHECKDB and send an email. Fortunately there is support to send email on Agent job failure.

As for how you want to implement such a job, is up to you. You can have your own job that you wrote where you do DBCC CHECKDB against the relevant databases. Or you can use Maintenance Plans. Or, as many of us do, use Ola Hallengren's scripts. There's nothing magical with Ola's scripts in this regard, you still have to configure that job to email on failure. There is a lot of other things that are nice with Ola's maint solution, though.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.