Today my good friend Marcos Freecia (@sqlfreccia) asked if there is some way to be notified if a database is taking off line.
I did not found any event to do this (if someone knows please tell me), BUUUUT every changes in the database options is logged in the SQL Server ERRORLOG and there is an event fired to a ERRORLOG.
Then was just find what error id is for the database options (5084) – Errors 5000 – 5999 and register the event :
Notice that now I’m specifying the event identifier (SourceIndentifier parameter) to unregister
Now, every change in the databases will be triggered when logged in the SQL Server ERRORLOG.
But what if I want every time that my server are restarted, this event is registered :
Thers is some ways. You can do it in by SQL Server Alerts, but you will depend the SQL Agent to send an email.
You can use the awesome project from Trevor Sullivan (@pcgeek85)
What is PowerEvents?
PowerEvents is a Windows PowerShell v2.0 module designed to facilitate the ease of creating, updating, and deleting WMI (Windows Management Instrumentation) permanent event registrations. PowerEvents makes it easy to create WMI event filters (define the events you want to capture) and event consumers (responders to events), and then bind them together to initiate the flow of events. By leveraging permanent event registrations, you can perform advanced monitoring functions on a workstation or server, that would otherwise require implementation of an enterprise monitoring product. Because WMI is incredibly vast in the information it provides, very detailed monitoring can be performed using almost any of the WMI objects that exist on a computer.
What are WMI Permanent Event Registrations?
A little-known capability of the WMI service, is its capability to create a permanent registration (listener) for events, and then automatically respond to those events. At a very basic level, it’s “if X happens, do Y” but in this case, it’s all built into WMI, without the need for any additional software.
In other words, you will switch from temporary consumers to a permanent consumer.
Or you can create a .ps1 with this script, put into a .bat file and excute using :
And use the noexit and windowstyle to hidden PowerShell.exe Console Help
Then, you can put into your server startup options.
and to unregister your event :
where ErrorEventLog is the name that I put in parameter SourceIdentifier in the Register-WMIEvent
The register-wmievent run a job in another runspace, then to find bugs in the code is a bit complicated. In my SQLBITS session I will show how to use the variable $Event and how to solve these bugs.
See you in Liverpool guys, do not miss my session, I am sure that you’ll like it