Monitor SQL Server Databases Changes Using WMI Alerts


By:   |   Updated: 2013-11-15   |   Comments (35)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Monitoring


Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


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 = ''[email protected]'', -- 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 Updated: 2013-11-15


get scripts

next tip button



About the author





Comments For This Article




Tuesday, September 01, 2020 - 1:14:33 AM - Saradhi Back To Top (86397)
Hi Svetlena -- Its working,
Thank you

Friday, August 28, 2020 - 10:41:31 PM - Svetlana Back To Top (86388)
Saradhi,

You will need to use something like this:
SET @Command =''$(ESCAPE_SQUOTE(WMI(TSQLCommand)))''

Make sure that the SQL Agent option to replace tokens is enabled in Agent's properties.

Friday, August 28, 2020 - 9:45:59 AM - Saradhi Back To Top (86378)
Hi Svetlena,

Thanks for the reply and it fixed my issue

I have one small question.

I am trying to use the scripts mentioned by you and also trying to insert the values into a table.

i need to capture the TSQL command for creating database or dropping database. how can i capture that

for example i am using below code snippet in the job but its not working.

SET @text = 'TSQL Command: "' + CommandText + '"'';
INSERT INTO TABLENAME
([SQL_Text] )
VALUES
(@text)

Tuesday, August 25, 2020 - 10:55:18 PM - Svetlana Back To Top (86362)
Hi Saradhi,

You will need to create 2 alerts with these WMI queries:
select * from CREATE_DATABASE

AND
select * from DROP_DATABASE.

Thanks for reading,
Svetlana

Tuesday, August 25, 2020 - 12:28:18 PM - Saradhi Back To Top (86358)
Hi Svetlena,

Thanks very much for this article. helped me a lot,

I need some help with the Alert.

I need to setup alert only for the Database Creation and Drop.

i do not require for ALTER_DATABASE events at all.

I tried delay between intervals to 15 but its not working for me and sending the alerts for ALTER statements.

I need a query something like Select * from DDL_Database_Events .... ( where .....columnname='CREATE_DATABASE').

Can you help with that please

Thursday, May 07, 2020 - 10:58:10 PM - Svetlana Back To Top (85598)

Hi Asher,

I am not very familiar with log forwarding, but to make it work with MSX/TSX you will still need to create WMI alert on each server. Alerts trigger jobs. The job could be pushed from the MSX server to the target servers though. The alerts don't generate log events, but you can include this as part of the response job.


Thursday, April 16, 2020 - 5:48:38 AM - Asher Back To Top (85386)

Hello,

great script. My only question is will this work in an MSX/TSX environment with log forwarding enabled on all TSX servers. 

Ideally we want to be notified when an auto-growth occurs on all TSX servers. 


Saturday, October 12, 2019 - 5:59:26 PM - Svetlana Golovko Back To Top (82754)

This is very good question.

This tip has a script for the alert creation with "@delay_between_responses=15" option. This was done in purpose to avoid multiple emails in case if multiple updates are done withing one session as in your post below. 

If you want to get emails for each "ALTER" statement just change it to  @delay_between_responses=0. 

Thanks,

Svetlana


Friday, October 11, 2019 - 3:21:00 AM - Madusudan Satapathy Back To Top (82734)

Thanks for providing such simple way to generate alerts for DDL commands. 

Issue: 

It is working fine when it comes to single ddl event. 

But, when I executed 3-4 ddl commands in a single query, it throws alert for the first ddl, not for the follwoing ones. Any suggestion how to capture multipe ddl events when they ran in under one session. 


Thursday, August 15, 2019 - 10:57:45 AM - Svetlana Golovko Back To Top (82067)

Hello,

It looks that you missed the first step - "Replace tokens for all job responses to alerts" option.

Note, that you may need to restart SQL Server Agent after changing it.

Thanks,

Svetlana


Monday, August 12, 2019 - 7:52:56 AM - Link Back To Top (82036)

I am also getting an error: Unable to start execution of step 1 (reason: Variable WMI(TSQLCommand) not found).  The step failed.

What did I miss?


Tuesday, December 12, 2017 - 11:04:08 PM - Svetlana Golovko Back To Top (73951)

 

 

Hi Mahesh

Are you trying to set this up on a Default or Named Instance? This tip has an explanation for this particular error.

Make sure you specify the namespace as "\\.\root\Microsoft\SqlServer\ServerEvents\INSTANCE1" if your SQL Server Name is something like SQLSRV\INSTANCE1.

Thanks,

Svetlana

 


Monday, December 11, 2017 - 12:55:41 AM - Mahesh Ingale Back To Top (73865)

Hi Svetlana,

I am getting the below error:

Msg 22022, Level 16, State 1, Line 3

SQLServerAgent Error: WMI error: 0x8004100e.

Msg 14511, Level 16, State 1, Procedure sp_verify_alert, Line 307

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.

I have verified the wmi namespace name from below query as well.

get-wmiobject -list -namespace "root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER"  | select-string -pattern "DDL_DATABASE_EVENTS" 

Could you please help me what would be the issue.

 


Wednesday, December 06, 2017 - 10:37:45 PM - Svetlana Golovko Back To Top (73705)

Hi Mahesh

DDL_DATABASE_EVENTS is not a table. It's Server Event Class that used by WMI. You don't need to create it and you can't directly query it with T-SQL. It exists as an event class and to use it you just need to create a WMI alert as described in the example above.

 

 Thanks,

Svetlana


Wednesday, December 06, 2017 - 5:17:37 AM - Mahesh Ingale Back To Top (73667)

Hello Svetlana,

Could you please tell me where to create DDL_DATABASE_EVENTS table to capture events. Also post details about table structure.

Regards,
Mahesh Ingale

 

 


Thursday, September 03, 2015 - 10:55:35 AM - rvsc48 Back To Top (38596)
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 (35357)

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 (34513)

 

Very Useful article. Thank you very much.


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

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 (34042)

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 (32848)

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 (32843)

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 (30102)

 

Thank you so much


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

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 (29907)

 

can we monitor for database restore?


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

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 (29477)

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 (28133)

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 (28120)

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 (28079)

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 (27705)

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


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

Thank you everybody for your comments!


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

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 (27516)

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 (27513)

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



download





Recommended Reading

How to setup SQL Server alerts and email operator notifications

Automate Monitoring SQL Server Error Logs with Email Alerts

Automate SQL Server Monitoring with Email Alerts

Monitor SQL Server Database File Growth with WMI Alerts

Monitor SQL Server Master Database Changes with WMI Alerts








get free sql tips
agree to terms


Learn more about SQL Server tools