Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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.).
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):
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 = ''[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):
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';
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:
- 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.
Last Update: 2013-11-15
About the author
View all my tips
- How to setup SQL Server alerts and email operator ...
- Automate Monitoring SQL Server Error Logs with Ema...
- Automate SQL Server Monitoring with Email Alerts...
- Monitor SQL Server Database File Growth with WMI A...
- Monitor SQL Server Databases Changes Using WMI Ale...
- Monitor SQL Server Master Database Changes with WM...
- More SQL Server DBA Tips...