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.


					
Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: