Monitor SQL Server Databases Changes Using WMI Alerts

Problem

We need to get notifications every time a database is created or deleted. Also, we would like to receive emails when there are any database level configuration changes. This will help us react to these changes immediately (for example, configure database backup for a new database, configure transaction logs backups when the database recovery mode is changed, update maintenance plans and inventory when the database is deleted etc.).

Solution

In this tip you can find out how to setup email alerts using DDL triggers when new databases are created. In our tip we will explain how to setup universal notifications for databases modifications using WMI alerts.

SQL Server Agent Configuration

Before we configure alerts and create a job that will send notifications we need to enable SQL Server Agent’s “Replace tokens for all job responses to alerts” option.  To do this right click on SQL Server Agent in SSMS and select Properties.  Then select the Alert System page as shown below and check the Token Replacement option and click OK to save the change.

This option also could be enabled using the following script:

EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens = 1
GO

This will allow SQL Server Agent to replace the tokens with running values (what database changed, who changed it etc.). Read more about using Tokens in Job Steps here (make sure you read the security note in the article and understand the security risk).

Configure Database Mail

We also have to make sure that Database Mail is configured for our SQL Server instance (note the “Mail Profile” name that we will use later in our job):

Read this tip about the Database Mail setup and configuration or this tip if you prefer to use the scripts for Database Mail configuration.

Create a SQL Server Agent Job

Now we are ready to setup a job that will respond to the alerts.  The job is named “WMI Response – DATABASE Class Event”.

The image below displays the job’s step. You will need to update @profile_name and @recipients parameters with your values (@profile_name will be the same as the “Mail profile” in the previous image):

Here is the complete script that you can use to create the job (update @profile and @recipients parameters to your values):

EXEC msdb.dbo.sp_add_job @job_name=N'WMI Response - DATABASE Class Event', 
  @enabled=1, 
  @notify_level_eventlog=0, 
  @description=N'Sends notifications to DBA when DATABASE DDL event(s) occur(s)', 
  @owner_login_name=N'sa'
EXEC msdb.dbo.sp_add_jobstep @job_name=N'WMI Response - DATABASE Class Event', 
  @step_name=N'Send e-mail in response to WMI alert(s)', 
  @step_id=1, 
  @subsystem=N'TSQL', 
  @command=N'DECLARE @class_string NVARCHAR(200), @str_body NVARCHAR(max), @xdoc INT, @doc NVARCHAR(max)
-- get TSQL Command Text from XML
SET @doc =''$(ESCAPE_SQUOTE(WMI(TSQLCommand)))''
EXEC sp_xml_preparedocument @xdoc OUTPUT, @doc
SELECT   @str_body = ''TSQL Command: "'' + CommandText + ''"; 
 Database Name: $(ESCAPE_SQUOTE(WMI(DatabaseName)));
 SQL Server: '' + @@SERVERNAME + '';
 Post Time: $(ESCAPE_SQUOTE(WMI(PostTime))); 
 Login Name: $(ESCAPE_SQUOTE(WMI(LoginName)));''
FROM       OPENXML (@xdoc , ''/TSQLCommand/CommandText'',1)
      WITH (CommandText  varchar(max) ''text()'')
EXEC sp_xml_removedocument @xdoc
-- identify type of the event
SELECT @class_string = ''"$(ESCAPE_SQUOTE(WMI(DatabaseName)))": $(ESCAPE_SQUOTE(WMI(__CLASS))) event''
  
-- send e-mail with database change details
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ''DBServerAlerts'', -- update with your value
    @recipients = ''dba_alerts@YourCorpDomain.com'', -- update with your value
    @body = @str_body,
    @subject = @class_string ;
', 
  @database_name=N'master'
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'WMI Response - DATABASE Class Event',  @server_name = @@SERVERNAME
GO

Setting up the WMI Alert

Now we are ready to setup the alert.  In SSMS, under SQL Server Agent, expand Alerts and right click and select New Alert.

• set the alert type to “WMI event alert”
• make sure you use correct WMI namespace (see below for more info):

Note: the namespace will be different for the default instance and for the named instance:

-- Example of the DEFAULT instance's namespace ("DEMOSQL1" SQL Server):
\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER
-- Example of the NAMED instance's namespace ("DEMOSQL1\INSTANCE1" SQL Server):
\\.\root\Microsoft\SqlServer\ServerEvents\INSTANCE1

If you get error message 22022 it means you specified an incorrect WMI namespace:

On the Response page you will need to add this value “WMI Response – DATABASE Class Event”, which is the job we just created above.

Here is the script for the alert including the alert’s response (to execute the job that we have created earlier):

EXEC msdb.dbo.sp_add_alert @name=N'WMI - Database DDL Events', 
  @message_id=0, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=15, 
  @include_event_description_in=1, 
  @notification_message=N'WMI - DB Change notification', 
  @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
  @wmi_query=N'select * from DDL_DATABASE_EVENTS', 
  @job_name=N'WMI Response - DATABASE Class Event'
GO

Make Sure Service Broker is Enabled

 One of the requirements for the WMI Provider for Server Events to work is to make sure that Service Broker on msdb database is enabled. It is enabled by default, but you can verify it by running this query:

SELECT is_broker_enabled  FROM sys.databases WHERE name = 'msdb';

Testing Alerts

Now we should be able to receive email notifications when there are any changes to the databases.

First test, lets create a new database:

CREATE DATABASE [_Demo_DB1]
GO

You will get an email that will look similar to this:

Now we will update the database recovery model to FULL:

ALTER DATABASE [_Demo_DB1] SET RECOVERY FULL
GO

You will get an email with a different subject:

Note: This alert does not include database object creation or modification events, only database configuration changes monitored by this WMI event.

The last test, delete the database:

DROP DATABASE [_Demo_DB1]
GO

The email notification will look like this:

If you prefer to monitor only single events such as (“CREATE DATABASE” or “ALTER DATABASE” or “DROP DATABASE”) you will need to setup your alerts with different WMI queries like below:

-- for "CREATE DATABASE"
select * from CREATE_DATABASE
-- for "ALTER DATABASE"
select * from ALTER_DATABASE
-- for "DROP DATABASE"
select * from DROP_DATABASE

Set alert’s response to the same job we created earlier:

Next Steps

• Read this tip about How to Automate SQL Server Monitoring with Email Alerts.
• Read this tip about How to setup SQL Server alerts and email operator notifications.
• Read another tip that describes setting up WMI alert for the Login Failed Events.
• Get familiar with “WMI Provider for Server Events Concepts“.
• Use other classes for your SQL Server events monitoring.

Svetlana Golovko

Svetlana has been working in IT for more than 17 years. Most of her career has focused on Database Administration (both SQL Server and Oracle) and Database Development. Databases are Svetlana’s passion, but she also has fun helping co-workers and friends in troubleshooting non-database related issues. Svetlana tries to explore and learn as many SQL Server features as possible. Her favorite SQL Server features are Policy Based Management, SSIS, SSRS and Master Data Services. One of Svetlana’s areas of expertize is cross systems / database integration.  Svetlana is currently a hands-on Database Team Lead in Calgary, Canada where she promotes SQL Server.

Svetlana likes to share her knowledge with others and enjoys learning herself. Her hobby is photography, but now she spends her free time away from Database Administration with her little girl who proudly wears her MSSQLTips shirt. Svetlana blogs at http://databaserefresh.com and posts her pictures to https://plus.google.com/u/0/111115767149899859037/posts.  Her Twitter account is @magasvs.

• MSSQLTips Awards: Rising Star (50+ tips) – 2018 | Author of the Year Contender – 2015-2017