In part 1 of this article series, we illustrated how to access one of the two SQL Server 2005 WMI Providers - WMI Provider for Configuration Management, using Window PowerShell. We also illustrated how to use the provider to perform administrative tasks.

In part 2 of this series, we are going to move on to the other SQL Server 2005 WMI provider - the WMI Provider for Server Events. We will show you how to monitor DDL and trace events in a SQL Server instance by leveraging this provider and Windows PowerShell.

The WMI Provider for Server Events provider lets you use the Windows Management Instrumentation (WMI) to monitor server events. The provider turns SQL Server into a managed WMI object and leverages event notification in SQL Server.

This provider manages a WMI namespace for each instance of SQL Server 2005. The name of the namespace is in the format root\Microsoft\SqlServer\ServerEvents\instance_name. For a default instance, the namespace is root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER. For a named instance Instance1, the namespace is root\Microsoft\SqlServer\ServerEvents\Instance1.

When WMI events in the namespace of a SQL Server instance are monitored for the first time, the provider creates a target service in the msdb database called SQL/Notifications/ProcessWMIEventProviderNotification/v1.0 and a queue for the target service WMIEventProviderNotificationQueue.

When a WMI management application issues a WMI Query Language (WQL) query to access SQL Server events, the WMI Provider for Server Events translates the query into an event notification. If events on the server level are queried, the provider creates a server event notification. If events in a database or on a particular database object are queried, the provider creates an event notification in the target database.

After the required event notification is created, it sends event data to the target service SQL/Notifications/ProcessWMIEventProviderNotification/v1.0 in the msdb database. The target service puts the event into the WMIEventProviderNotificationQueue queue in the msdb database. The provider reads the XML event data from this queue and transforms it into managed object format before returning it to the client application.

Because Service Broker services are used by the event notification to send messages about server events, Service Broker must be enabled in the msdb database and the target database wherever the events are generated. To check if the service brokers are enabled on a SQL Server instance, and to get the broker instance GUID in each database, run this query on the instance.

If the is_broker_enable column for a database has a value 0, it means that Service Broker is not enabled for the database. To enable Service Broker for a database, for example, the AdventureWorks database, use the ALTER DATABASE statement.

ALTER DATABASE AdventureWorks SET ENABLE_BROKER

The service_broker_guid column contains the service broker GUID in each database. The broker instance in the msdb database is the most useful because msdb hosts the target service and the service queue.

WMI Query Language (WQL)

The WMI Query Language (WQL) is designed to perform queries against the CIM repository to retrieve WMI information. WQL is a subset of ANSI SQL with minor semantic changes to support WMI. Therefore, it is very straightforward to write WQL queries.

WQL queries can be divided into three types: data, event and schema.

Data queries are used to retrieve class instances and data associations. Every Get-WmiObject command we have seen in Part 1 of this series can be mapped to a data query - a Select statement with the "-filter" parameter being mapped to a "where" clause.

Event queries are used to subscribe to WMI events and will be used in this article. Event providers use event queries to register to one or more events. Event queries are not supported in Windows PowerShell v1. For now, we need to use Windows PowerShell in conjunction with .NET classes to monitor WMI events.

Schema queries are used to retrieve class definitions and schema associations. We are not going to cover this kind of queries in this series.

Event Handling with .NET and Windows PowerShell

Since Windows PowerShell v1 does not support event queries, we need to create a temporary event consumer in .NET from the System.Management.ManagementEventWatcher class. The constructor of this class accepts two objects - a System.Management.WQLEventQuery object specifies a WQL query to retrieve events of interest, and a System.Management.ManagementScope object specifies a namespace to watch.

For example, to subscribe to the DDL_DATABASE_LEVEL_EVENTS event class in the AdventureWorks database on the default instance on a computer PowerPC, the WQL query is:

To create a System.Management.WQLEventQuery object associated with this WQL query, execute this command:

$objWql = New-Object System.Management.WQlEventQuery $wqlQuery

The events of the default instance on a computer named PowerPC are available under the namespace \\PowerPC\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER. We create a System.Management.ManagementScope object to specify this namespace for monitoring.

After both the WQLEventQuery and ManagementScope objects are created, we construct a new System.Management.ManagementEventWatcher object and start the watcher. The watcher therefore subscribes to events through the given query and delivers them.

The WaitForNextEvent method of the watcher waits for the next event that matches the specified query to arrive, and then returns it. An event object is different from a data object. You cannot use * to select all of its properties. You need to select the properties explicitly by their names. For example,

To retrieve different events, we only need to change the particular WQL query, the namespace and the selected properties of the events. Therefore, we encapsulate the above steps in a function, and reuse the function to create a watcher to monitor different events throughout this article. The complete function Get-WMIEvent.ps1 is shown here.

In this function, we create a watcher object based on the given WQL query and namespace. Then we catch the events in an infinite loop. We also allow the function to exit by pressing the Esc key. The default time interval of monitoring is set to 10 seconds.

To reuse a function, the easy way is to put it in your profile. Let's include the function in the user-specific, shell-specific profile - %UserProfile%\My Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1 or %UserProfile%\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1 on Windows Vista. By default, the built-in variable $profile stores the path and file name of the profile. To see the value of the $profile variable, type:

If the profile exists, the command returns True. Otherwise, we need to create the profile file using this command:

PS > new-item -path $profile -type file -force

After the profile specified in the $profile variable has been created, you can enter aliases, functions, and scripts in the profile to customize your shell. To run the scripts in this article, put the Get-WMIEvent function in the profile. All the scripts included here only print out event information in the console for demonstration purposes. In real practice, you can save the output in a tracing table, notify support personnel through e-mail or page based on the output, or send an alert to an event management system such as Netcool.

Monitoring errors from the SQL Server error log

The errors from the SQL Server error log help us detect any current or potential problems, including data file growth problems, backup device problems, failed logins, insufficient lock resources, and so on. The trace event class for the SQL Server error log is ERRORLOG. In the script that follows, called MonitorErrorLog.ps1, errors from the SQL Server error log are captured and printed out. The backstroke characters (`) are used to concatenate the script lines.

This script prints out all the properties that are available for the ERRORLOG class. Let's test it by executing a large query against the default instance on the local computer. The tempdb database cannot allocate enough space for the temporary objects used in the query execution and thus SQL Server writes an error in the error log. The script captures the error and prints it out.

We can see from the output, the login PowerPC\Yan executed a query against the AdventureWorks database. The query caused the primary filegroup of the tempdb database to grow and exceed its maximum size. The Error, Severity, and TextData columns provided us the complete error information. After we are notified of the error, we can either adjust the settings of the tempdb data files or work with users to improve the query. In real practice, you might want to capture only errors with severity levels 17 and higher that indicate software or hardware errors. You can achieve this easily by adding a where condition on severity to the WQL query.

Monitoring Deadlocks

A deadlock occurs when two or more sessions permanently block each other by each session having a lock on a resource, which the other sessions are trying to lock. SQL Server Database Engine has a lock monitor thread that periodically initiates a search through all of the tasks to detect deadlocks. After a deadlock is detected, the Database Engine ends a deadlock by choosing one of the threads as a deadlock victim, and a 1205 error will be thrown by SQL Server. To collect the information about the threads and the resources involved in the deadlock, we can monitor the DEADLOCK_GRAPH trace event class. The MonitorDeadlocks.ps1 script shown here collects the deadlock information.

This spid holds exclusive locks on the Test2 table, and tries to update the Test table. We have a deadlock situation here since spid 53 and spid 54 are blocking each other and trying to update the table the other process is holding. Our script detects the deadlock and prints out the deadlock graph.

Spid 21 shown in the SPID property was the lock detection thread initiated by SQL Server and was run under the sa login. The XML text in the TextData property might look cryptic. However, if you examine it carefully, the first <process> tag showed you the winner in this deadlock situation - spid 54, and the login PowerPC\Yan owned this spid. The <frame> tag inside the process tag showed you the statement spid 54 was executing at the time of deadlock. The <inputbuf> tag showed you the entire input buffer from spid 54. The second <process> tag showed you the victim - spid 53. Again, the <frame> tag showed you the statement spid 53 was executing at the time of deadlock and its entire input buffer. The <resource-list> tag showed you the resources each process was holding exclusively on and the resources it was waiting for. Although only process ids, processd7aa78 and processfc9108, were included in the <resource-list> tag, you can match them easily with spid in the <process> tag.

Monitoring Blockings

The trace event class BLOCKED_PROCESS_REPORT can be used to monitor blocked processes. However, by default, this event class is disabled. You need to run sp_configure to reconfigure the blocked process threshold option. This option specifies the threshold, in seconds, at which blocked process reports are generated. For example, if you want a blocked process report to be generated for each task that is blocked for 30 seconds, run this query on the SQL Server.

Please note that each report only contains two connections of a blocking. Unlike the DEADLOCK_GRAPH event, which shows you a deadlock chain, this event class doesn't show you the complete chain. You have to work through all the reports you gather at the same time to figure out which process is the head of the chain.

Similar to the deadlock graph we have seen in the last section, spid 5 shown in the SPID property was the blocking detection thread initiated by SQL Server. The XML text in the TextData property showed you the blocked process in the <blocked-process> tag, and the blocking process in the <blocking-process> tag. Each of these two tags showed you the login owned the session and the input buffer of the spid. The ObjectID property identified the ID of the resource that the waiter waited for - 1207675350. To determine the database the resource belonged to, look at the currentdb id inside the <process> tag. In this case, the currentdb id was 5, which corresponded to the AdventureWorks database. To determine the resource, run this in the AdventureWorks database.

Select * from Object_name(1207675350)

The Test table is returned. Therefore, the output showed us that spid 54 was waiting for the Test table to be released by spid 53.

Monitoring login changes and failed login attempts

As a DBA working at a financial company, I often face questions from BU on security auditing. They usually concern about unauthorized server access or malicious security exploitation. To help them meet the auditing requirements, I can create SQL Server traces using extended procedures, such as sp_trace_create and sp_trace_setevent, to monitor login events in the background. However, I am going to show you another approach to monitor login changes and failed login attempts with the WMI Provider for Server Events, which is easier and cleaner.

DDL_LOGIN_EVENTS is the DDL event class for login events. It has three child classes.

In this script, we added a general property __CLASS to identify if a login is altered, created or dropped. This is necessary because the three child classes don't return any class-specific properties to identify the type of action, except that DROP_LOGIN returns the TSQLCommand executed to drop the login.

The script prints out the type of events that happened, the login that was changed, and the login that issued the change. From the information, we can track down unplanned or malicious changes.

In addition to login changes, we also want to audit failed login attempts. The trace event class AUDIT_LOGIN_FAILED is for this purpose. The MonitorFailedLoginAttempts.ps1 script shown here captures failed login attempts.

The TextData property showed you the same message as in the pop-up window. The ComputerName property showed you which workstation the login was attempted from. By capturing all the failed login attempts, we can track down malicious security exploitation.

Monitoring database changes

On the server level, we need to audit database events to ensure no accidental database deletions. In a shared database environment, we also need to monitor additions of new databases in order to manage backups and disk space effectively.

Database events are included in the DDL_SERVER_LEVEL_EVENTS class. Three child classes of this class associated with database changes are listed below.

ALTER_DATABASE: Captures changes to properties of databases.

CREATE_DATABASE: Captures new database creations.

DROP_DATABASE: Captures database deletions.

There are other child classes under the DDL_SERVER_LEVEL_EVENTS class. To retrieve only events from the above three child classes, we need to use the identifier __CLASS to filter the events from the DDL_SERVER_LEVEL_EVENTS class. The sample script MonitorDatabases.ps1 is shown here.

The DatabaseName property showed the database that was being changed. The TSQLCommand property showed the actual T-SQL statement that was run against the server. You can also see the details of the sessions that made the changes.

Monitoring database objects

In a development team where each developer can make their own changes to database schema, a developer might make unplanned changes and thus overwrite the work done by another developer. To ensure the change processes transparent and manageable, we should be able to track down planned and unplanned changes to minimize the risk of improper changes causing database outage. This problem magnifies itself in dealing with stored procedures since SQL Server does not keep track of the last time a stored procedure was modified and the login that made the modifications.

The DDL_PROCEDURE_EVENTS class is the event class for stored procedure events. It has three child classes.

The query first creates a stored proc called getBlockedProcessesDetails. This stored proc gets a list of blocked and blocking sessions. Then the stored proc is modified to include the T-SQL command from each sesssion. Finally, the stored proc is dropped.

The ObjectName property showed which stored procedure was changed. The TSQLCommand property showed the actual T-SQL statement that was run against the database to change the stored procedure. The LoginName property showed the login that made the changes.

Conclusion

We have illustrated above the power of Windows PowerShell in conjunction with the WMI Provider for Server Events. SQL Server 2005 WMI providers have changed and improved a lot from SQL Server 2000. The providers can be very useful in your daily work as a DBA. The scripts from this series can be easily expanded to deal with more difficult tasks.

Yan Pan is certified in Microsoft database certifications, including MCTS in SQL Server 2005 and MCDBA. She has more than 5 years of experience in SQL Server administration and Analysis Server. Currently, Yan is the primary SQL Server DBA in one of the top finance companies on Wall Street.