Archive for September, 2013

Subscribing to SQL Server WMI Events

As mentioned in http://technet.microsoft.com/en-us/library/ms181893.aspx the WMI provider for Server Events allows you to use WMI to monitor events in SQL Server. The events you can monitor are listed in sys.trigger_event_types (http://technet.microsoft.com/en-us/library/bb510452.aspx). Using SQL Server functionality you can monitor these events using DDL triggers and event notifications. SQL Agent WMI alerts can also be used to react to these server events.

An alternative, much quicker way to monitor these events is through Powershell. This short scripts allows you to monitor UPDATE_STATISTICS events:


$computer = “yourcomputername”
$sqlevent = 'UPDATE_STATISTICS'
$query = “SELECT * FROM $sqlevent”
$filename = “c:\temp\" + $sqlevent + ".csv”

# Set up the action that should be taken when the event is raised, in this case writing to a CSV file
$action = { $event.SourceEventArgs.NewEvent | select-object -property * | export-csv $event.MessageData -NoTypeInformation -Append }

# Register for the event - if you're using a name instance replace MSSQLSERVER with the instance name
Register-WmiEvent -Namespace ‘root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER’ -Query $query -Action $action -MessageData $filename -Computer $computer -SourceIdentifier “$sqlevent $computer”

# Check that the registration has been successful
Get-EventSubscriber

# Now run an UPDATE STATISTICS command then examine the c:\temp\UPDATE_STATISTICS.csv file
# When you’re finished unregister from the event

Get-EventSubscriber | Unregister-Event

# or use this to unregister from specific events

Unregister-Event -SourceIdentifier “$sqlevent $computer”

So just by setting five variables and making one call to Register-WmiEvent you have a monitoring solution not just for UPDATE STATISTICS but also for any of the other SQL Server events you wish to monitor.


			

Leave a comment