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.