The Road to Awareness (aka Event Notifications)

Getting notified of what is going on is critical to meet SLAs. Plus, who wants to be the second to know about something going on with a production sql server? No thanks.

SQL Alerts serve their purpose. The cache hit ratio and login failures are great. The disk IO and getting alerted to higher severity errors are also important indicators. Of course the alerts can’t do everything.

While having sql which runs regularly to check on this or that is important, there’d be an awful lot of jobs needed to check on everything. Currently for me this is: what is filling up tempdb; database mirroring which is not synchronized or synchronizing; and free space on the sql server drives.

Back in the day I used MS Access and SQL DMO to make a morning check thing of sorts. There were no Central Management Servers back then either so I also added the capability for deployments and logging the outcome. At this particular employer there were many sql servers around the world all with bidirectional transactional replication. Schema changes were no joke.

Then came powershell and another dba and I wrote (and copied from the interwebs) the code to duplicate the checking functionally which outputted to a spreadsheet. Truthfully, while I was glad to get rid of Access, the spreadsheet was a pain as there was no way to store history in a way that could be queried. Seeing patterns is a very important. We need data though before a pattern can be seen!

Then I wrote the sql to replace the powershell. Also at this time database triggers came to be and they allowed for object modification history. While this was ok, the biggest risk – having to wait until the next day – was still present. I simply like to know when files are growing.

Happily now we have Event Notifications. Really nice. The biggest hurdle was writing the stored procedure which would be triggered as the MS documentation was not a ton of help. Here is mine. It is taken from several articles, most notable this one and this one. Note that the procedure expects other objects; use this script to create the service broker objects. There are also some tables in the procedure for adding email recipients for specific databases and logging the events themselves. This will make the tables. The procedure assumes you have a default, public database mail profile.

This entry was posted in DBA, SQL. Bookmark the permalink.

2 Responses to The Road to Awareness (aka Event Notifications)

  1. Almas says:

    My spouse and i have been relaly relieved when Albert managed to complete his researching out of the ideas he obtained out of the site. It’s not at all simplistic to simply happen to be freely giving instructions that other people may have been selling. And we fully grasp we’ve got the writer to appreciate for that. Those illustrations you have made, the easy web site navigation, the relationships you will help to foster it is most awesome, and it is aiding our son in addition to the family imagine that that matter is enjoyable, and that is exceptionally vital. Thanks for all!

Leave a Reply

Your email address will not be published. Required fields are marked *