Many times I've been asked to do a blog post about creating Agent alerts, and given that today I demo'd it as part of our Accidental DBA workshop at Connections, it seemed a good time to do the blog post too!
I demo this in the context of alerting a DBA when an 823 or 824 IO error occurs. One of my early blog posts (see here) explains what these are, as well as providing a corrupt database that you can use to see these errors happening.
The idea is that I want to know as soon as an IO error occurs so I can start recovering and take preventative action to stop it happening again. I don't want to rely on users telling me when a query hits an IO error, and I don't want to have to scan the SQL error logs to find them. So I'm going to create an alert.
The first step is to fire up Management Studio and make sure SQL Server Agent is running. Next we need to make there's actually an Operator defined – so the new alert has someone to actually alert! – so we'll use the New Operator wizard (see below for how to get there).
In the New Operator Wizard that appears, I've created an operator named 'SysAdmin'. There are three Notification Options you can use – email, net send, and pager. I've setup SysAdmin to use net send to my local machine. You need to make sure the Messenger service is enabled otherwise net send will not work. Also, be aware the net sends will NOT work unless the machine has a network connection – even if the net send source and destination are the same machine! Given the various issues with net send, it's better to use email or pager alerts – but for the purposes of this blog post its the easiest option.
Now let's create the new alert – using the New Alert wizard (see the below for how to get there).
In the New Alert Wizard that appears, I've created an alert named 'IO Errors' for all severity 24 errors on all databases. Below is a portion of the General tab of the wizard showing these settings:
I also need to specify what happens. In the Response tab of the wizard I've set the SysAdmin operator to be notified using net send. Again, see below.
In the Options tab I've checked the box to include the error text in the net send.
Now let's test it. Using the database called 'broken' that I provide as an example (see here), I'll force an IO error to occur. In my query window I get:
SELECT * from broken..brokentable;
GO
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7232c940; actual: 0x720e4940). It occurred during a read of page (1:143) in database ID 10 at offset 0x0000000011e000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\broken.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
And a few seconds later I get the net send:
Pretty cool!
You can also use the WMI Provider to do this – see Creating a SQL Server Agent Alert by Using the WMI Provider for Server Events.