SQL Server 2005 introduced the concept of events and added a WMI provider only to works with events in SQL Server . You probably know as Extended Events.
“The WMI Provider for Server Events works by translating WQL queries for events into event notifications in the database that you target. Event notifications, like the WMI Provider for Server Events, are new in SQL Server 2005. An understanding of how they work can be useful to you when programming against the provider. For more information, see Event Notifications (Database Engine). “
So this means that I can use this provider in my PowerShell Scripts
If you query in SSMs:
you will find all the events that you can interact, and one of them is SP_RECOMPILE.
Looking at BOL, we will find the resource that we need to start to play
From this I can create a table in SQL Server that will receive data when an recompile event occurs . I chose only some information that for me are the most necessary
Then I created a Module to Save at this table.In this module I have 3 functions.
Get-SQLRecompileEventSubClass : switch EventSubClass id to it description
Ex : EventSubClass 1 = Schema Changed, EventSubClass 2 = Statistics Changed
Get-ObjectType : switch objectType id to it description
Ex : ObjectType 8259 = Check Constraint , ObjectType 8262 = Stored Procedure
Get-SPRecomopile: Select the data formated from $event variable (this variable is automatically created by PowerShell with all info about the event)
So now it is just play :
Lets Register the Event and Save to SQL Table using Out-DataTable and Write-DataTable
And I create a SP that recompiles :
Then it is just exec this procedure and see the table :
Can you image what you can monitor ? Database Mirroring, replication, Data File Auto Grow …ha this is cool
It is just we use BOL – Data File Auto Grow Event Class
Include the Data File Auto Grow event class in traces that are monitoring growth of the data file.
If I want to a specific database, query “ and databaseid = yourdatabaseid”
Again, a small video with good music