WMI Tracing

A lot of companies find the need to monitor all DDL and DML activity on the server for Compliance reasons. Though SQL Profiler Traces provides this functionality by using Stored Procedures and Functions. These system stored procedures can be used from within your own applications to create traces manually, instead of using SQL Server Profiler. This allows you to write custom applications specific to the needs of your enterprise. However, you might choose not to use this approach due to the overhead of running profiler traces on the server. You can use server side profiler traces which tend to have a lesser performance impact on the server.

Another method is to use WMI Events to monitor SQL Server Events. Consider the following example, let's say you want to monitor the SQL Server events for all Update Statistics Events. Then you can use script below to create the WMI Alert and also the job to put the event details into a SQL Server database table.

<script>

USE TestDB
GO

-- Creating the table to store the DDL Events

CREATE TABLE [dbo].[UPD_STATS_Events](
[AlertTime] [datetime] NULL,
[SPID] [int] NULL,
[DBName] [nvarchar](100) NULL,
[TextData] [nvarchar](max) NULL
) ON [PRIMARY]

GO

-- Adding the job to run when the WMI Alert is raised

EXEC msdb.dbo.sp_add_job @job_name=N'Capture Update Statistics Events',
@enabled=1,
@description=N'Job for responding to DDL events' ;
GO

-- Adding the job step

EXEC msdb.dbo.sp_add_jobstep
@job_name = N'Capture Update Statistics Events',
@step_name=N'Insert data into Update Statistics Events table',
@step_id=1,
@on_success_action=1,
@on_fail_action=2,
@subsystem=N'TSQL',
@command= N'INSERT INTO UPD_STATS_Events
(AlertTime, Spid,DBName,TextData)
VALUES (getdate(), $(ESCAPE_NONE(WMI(SPID))),
''$(ESCAPE_SQUOTE(WMI(DatabaseName)))'',
''$(ESCAPE_SQUOTE(WMI(TSQLCommand)))'')',
@database_name=N'TestDB' ;
GO

-- Set the job server for the job to the current instance of SQL Server.
EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture DDL Events' ;
GO
-- Add an alert that responds to all DBCC events for
-- the default instance. To monitor deadlocks for a different instance,
-- Change MSSQLSERVER to the name of the instance.
-- For the named instance you need to use \\.\root\Microsoft\SqlServer\ServerEvents\<INSTANCE NAME>

EXEC msdb.dbo.sp_add_alert @name=N'Respond to DDL Events',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'SELECT * FROM UPDATE_STATISTICS',
@job_name='Capture DBCC Events' ;
GO

</script>

Whenever an update statistics task is fired against a database, the time when the command was executed, the SPID number, the database name and the T-SQL Command command associated with the event would be put into the table in the TestDB database.

Service Broker should be enabled for the MSDB and the database in which you are storing the event details.

Information about the namespaces can be found on MSDN. Also, you could use WMI Code Creator to browse through the namespace and the classes available and their properties.

Furthermore, you can also use the WMI Event Watcher Task or the WMI Data Reader Task of SQL Server 2005 SSIS to perform the same.