Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Monitor SQL Server Databases Changes Using WMI Alerts


By:   |   Read Comments (20)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More > Monitoring


Latest on-demand video "Optimize SQL Server Performance" (watch now for free)


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.

SQL Server Agent properties

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):

Database Mail and Mail profile

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):

Job Step

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):
Alerts setup

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:

Incorrect namespace error

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:

Create DB E-mail

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:

Alter DB E-mail

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:

Drop DB E-mail

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

Alert

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

Alert response
Next Steps


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, September 03, 2015 - 10:55:35 AM - rvsc48 Back To Top
Great scripts, thanks for the contribution!  They worked very well in my environment.

Thursday, November 20, 2014 - 2:56:42 PM - Ankit shah Back To Top

Hi Svetlana this very nice article and helpful for SQL DBA's . one quick question is that when I pur this query select * from CREATE_DATABASE or select * from DROP_DATABASE I get belowe error. 

The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax . 

It works well for this query  . elect * from DDL_DATABASE_EVENTS . I tried same thing in SQL 2005 , 2008R2 , 2012 but getting same error. 

Any resolution on this?

Thanks again



Friday, September 12, 2014 - 3:16:59 PM - Srinivas Back To Top

 

Very Useful article. Thank you very much.


Saturday, August 16, 2014 - 10:07:22 AM - Svetlana Golovko Back To Top

Hi Sunny,

 

I have a link in my tip pointing to the Microsoft article explaining security risk and how to avoid it. Here is a quote from it:

"Any Windows user with write permissions on the Windows Event Log can access job steps that are activated by SQL Server Agent alerts or WMI alerts. To avoid this security risk, SQL Server Agent tokens that can be used in jobs activated by alerts are disabled by default. These tokens are: A-DBN, A-SVR, A-ERR, A-SEV, A-MSG., and WMI(property). Note that in this release, use of tokens is extended to all alerting.

If you need to use these tokens, first ensure that only members of trusted Windows security groups, such as the Administrators group, have write permissions on the Event Log of the computer where SQL Server resides. Then, right-click SQL Server Agent in Object Explorer, select Properties, and on the Alert System page, select Replace tokens for all job responses to alerts to enable these tokens."

So, if you configure access to the event log correctly/secure then this should not be a problem.

Thanks for your comments, Svetlana


Thursday, August 07, 2014 - 12:54:29 PM - Sunny Back To Top

Good article Svetlana :). I have some doubts.

Is there any impact on SQL server while enabling the token replacements?

From security point of view enabling tokens is good or not?


Wednesday, July 23, 2014 - 8:02:51 PM - Svetlana Golovko Back To Top

Hi Norman,

 

Yes, it works the same way in SQL 2005. I have been using this for years starting with SQL Server 2005.

Is this default instance?

 

Svetlana

 


Wednesday, July 23, 2014 - 3:15:51 PM - NormanHeyen Back To Top

This is really slick and thanks for posting it.

I have it working fine in my SQL 2012 instance but I can't get around the error message (If you get error message 22022 it means you specified an incorrect WMI namespace:) in my SQL 2005 instance.

I copy and pasted the code and tried to build the Alert manually with the same results.

Does this work with SQL2005?

Thanks!

Norman


Friday, April 18, 2014 - 10:21:25 AM - Praveen Back To Top

 

Thank you so much


Friday, March 28, 2014 - 2:01:11 PM - Svetlana Golovko Back To Top

Hi Praveen,

Yes, you can do this as well. The query in alert will be "select * from AUDIT_BACKUP_RESTORE_EVENT".

Th eresponse job will have to be modified as well. Check this link for the columns that could be used in the response job: http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd

 

Svetlana


Thursday, March 27, 2014 - 9:48:14 PM - Praveen Back To Top

 

can we monitor for database restore?


Monday, February 17, 2014 - 6:44:44 PM - Svetlana Golovko Back To Top

Thank you, Dexter.

Yes, I have received an alert when I created the test SharePoint database using Central Management Administration ("Manage Content Databases"). I am surprised DDL triggers don't catch this. I haven't tested it, but I would think the triggers use the same event on background. Something to play with...


Monday, February 17, 2014 - 3:05:14 PM - dj Back To Top

Very nicely done, Svetlana.  Thank you for sharing this.

Do you happen to know if this will fire when SharePoint creates a database?

DDL triggers don't catch this.  :(

 

Cheers.

Dexter


Saturday, January 18, 2014 - 5:02:05 PM - Svetlana Golovko Back To Top

Nelson,

 

Do you run the job manually? To test the job you need to create or modify a database. This will trigger event and populate variable.

 

Svetlana


Friday, January 17, 2014 - 11:05:49 AM - Nelson Back To Top

I'm getting this: 

Message
Unable to start execution of step 1 (reason: Variable WMI(TSQLCommand) not found).  The step failed.

 

what's wrong?


Wednesday, January 15, 2014 - 12:48:41 AM - ananda Back To Top

Thanks for posting this wonderful article,, really helps to DBA and saving cost third party tool. 


Thursday, December 05, 2013 - 2:43:56 PM - Victor Back To Top

Bravo, Svetlana ! Very nice and elegant solution ! Спасибо. :)


Wednesday, November 20, 2013 - 7:51:44 PM - Svetlana Golovko Back To Top

Thank you everybody for your comments!


Wednesday, November 20, 2013 - 3:12:02 PM - Golam Back To Top

I've made sps to track changes of a database but your solution is very simple and well done. Pls keep posting.


Friday, November 15, 2013 - 10:41:44 PM - Mbourgon Back To Top

Ha! That is clever as hell!  I really thought I knew all the ways to track code changes - Audit, DDL Triggers, Event Notifications, XE, Traces...but that's a new one on me!  Very well done.


Friday, November 15, 2013 - 4:12:33 PM - Srinath Back To Top

Such a cleanly written article...So much helpful.. !!


Learn more about SQL Server tools