Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Email alerts when new databases are created in SQL Server


By:   |   Last Updated: 2013-01-14   |   Comments (18)   |   Related Tips: More > Triggers

Problem

Often times as a SQL Server DBA we find databases that appear on our servers that are not part of our backup plan or maintenance plan. This could be a DEV server where developers have CREATE DATABASE (MSDN) permission or other applications where Admins can create databases using their Admin console, such as SharePoint. I often struggled with this and decided to figure out a way to be alerted when a new database appears on my servers.

Solution

The solution I found to be the most useful was using a DDL Trigger (MSDN) to capture the event of the CREATE DATABASE (MSDN) command, combined with sp_send_dbmail (MSDN) to email the DBA team once the event occurs. This way we can set these new databases up in our daily backups and maintenance plans and will not be caught off guard when someone requests a restore of one of these databases. I have seen this very useful on SharePoint servers as databases seem to appear when the SharePoint admin creates new site collections (and not alerting the DBA team of course). This assumes you have database mail installed and configured. Details on this can be found in this previous MSSQL tip.

Basic DDL Trigger

The following code uses the EVENTDATA() routine which only returns data when called directly inside of a DDL or Logon trigger. It returns NULL if called anywhere else. This will capture the DDL statement into a variable.

USE master
GO
CREATE TRIGGER trg_DDL_CreateDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
SET @results = 
  (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
GO

This only captures the event, but provides no means of notification to the DBA team.

Advanced DDL Trigger

The following code below is the finished version of the DDL trigger that uses the basic DDL trigger above and adds personalization about the server name and who created the database and sends an email to the DBA team.

USE master
GO

CREATE TRIGGER [ddl_trig_database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
declare @subjectText varchar(max)
declare @databaseName VARCHAR(255)
SET @subjectText = 'DATABASE Created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @results = 
  (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))

--Uncomment the below line if you want to not be alerted on certain DB names
--IF @databaseName NOT LIKE '%Snapshot%'
EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'SQLAlerts',
 @recipients = '[email protected]',
 @body = @results,
 @subject = @subjectText,
 @exclude_query_output = 1 --Suppress 'Mail Queued' message
GO

The following items will need customization in your environment.

  • @profile_name = The database mail profile to use to send the email.
  • @recipients = The distribution group email address to send the email to within your organization.

Email Notification

Below is a screenshot of the email I received when I created a new database in my test environment. As you can see it has the Server the database was created on as well as the username indicating who created the database. Since the DDL is included in the email as well you can closely monitor the file locations if your corporation uses standards for data and log file locations as well.

Email Notification
Next Steps
  • Check your environment to see if you have any databases that have not been backed up.
  • Verify you have Database Mail installed and configured.
  • Test this DDL trigger in your DEV/Test environment to verify desired results are obtained.
  • Read more tips on DDL Triggers
  • Read more tips on Database Mail


Last Updated: 2013-01-14


next webcast button


next tip button



About the author
MSSQLTips author Chad Churchwell Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

View all my tips




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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, November 02, 2017 - 1:06:08 PM - Sean Perkins Back To Top

 

AWESOME!  Thank you very much, Greg!!


Thursday, November 02, 2017 - 12:11:00 PM - Greg Robidoux Back To Top

Hi Sean,

In SSMS go to Server Objects > Triggers and you will see the server side triggers.

You can also use T-SQL code like this to drop it.

DROP TRIGGER [trg_DDL_CreateDatabase] ON ALL SERVER
GO

You can use this T-SQL to get a list of triggers.

select * from master.sys.server_triggers

 

Thanks
-Greg


Thursday, November 02, 2017 - 10:54:40 AM - Sean Perkins Back To Top

 

I've tried the following TSQL code and it produced not result in Master or MSDB:

 

EXEC sp_msforeachdb 'select * from ?..sysobjects where xtype=''tr'''


Friday, October 27, 2017 - 10:49:39 AM - Sean Perkins Back To Top

Where in the Master database is this trigger located?  I was performing some tests and now I want to delete it and reconfigure it.  I went to the folder, under Master, named Database Triggers and it isn't listed there.


Wednesday, August 31, 2016 - 5:45:21 AM - Tapasi Behera Back To Top

 Hi,

Once the alert is recieved, I would like to take a full backup of the database that is created on the server. Is there a way it can be automated in the same trigger like.. if any database is created the trigger will shoot an alert and take a full backup immediately. Please help.

Problem - databases are created from application anytime and as we have differential backups running daily, the job fails as there would be no full backup of the new DB created. To avoid any involvement of taking manual backup can the trigger do the job.

 

Regards,

Tapasi

 

 

 


Wednesday, June 25, 2014 - 8:04:23 AM - Manoj Pawar Back To Top

hi

My name is manoj i want to send mail when user expired date is over and send mail to this user using trigger


Friday, August 02, 2013 - 1:11:31 PM - Luke MacDonald Back To Top

Thanks.  Found them shortly after writing the post.  Hate it when that happens.


Friday, August 02, 2013 - 11:34:22 AM - Greg Robidoux Back To Top

@Luke - the trigger is created in the master database if you ran the scripts as they are above.

In SSMS you can see server wide triggers under Server Objects > Triggers

Or run this query:

select * from master.sys.server_triggers


Friday, August 02, 2013 - 9:57:19 AM - Luke MacDonald Back To Top

This is awesome, thank you!

Where is it saved, I can't find it under any databases triggers folder.  I am not surprised since it's a instance wide trigger.

 

select * from sys.triggers 

 

The above doesn't return it either though.  Thanks


Monday, June 03, 2013 - 1:51:55 PM - Scott Gleason Back To Top

Nice, this is exaxtly what I was looking for.

 

Thanks Chad!

:-) Scott


Wednesday, February 20, 2013 - 2:18:45 PM - Jason Back To Top

Thanks again Chad.  I'd just like to finish with I've created a generic Event Notification processing system that doesn't rely on Server Triggers to send notifications via email when Databases are created and dropped.  It also handles anything that Server Triggers can handle, like logins, role changes, even Deadlock notifications.  I thank you, you kicked me in the right direction, and made me think about this more.


Thursday, January 17, 2013 - 8:41:26 AM - Chad Churchwell Back To Top
Thanks for everyone's input. I have worked out 2 different issues. One was the message of "Mail Queued" when a database is created. This can be resolved by adding an additional parameter to the sp_send_dbmail SP call called @exclude_query_output so your call should look like below

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLAlerts',
@recipients = '[email protected]',
@body = @results,
@subject = @subjectText, @exclude_query_output = 1


Also to address the question about snapshots, or databases that have "Snapshot" in the name. Below is a new version of the trigger that takes that into account (along with the fix above)

CREATE TRIGGER [ddl_trig_database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
declare @subjectText varchar(max)
declare @databaseName VARCHAR(255)

SET @subjectText = 'DATABASE Created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME()
SET @results = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))

--make sure database does not have word Snapshot in it before sending mail
IF @databaseName NOT LIKE '%Snapshot%'
     EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'SQLAlerts',
     @recipients = '[email protected]',
     @body = @results,
     @subject = @subjectText,
     @exclude_query_output = 1  --Suppress 'Mail Queued' message


GO


Wednesday, January 16, 2013 - 5:02:59 PM - David Back To Top

Chad,

I tried your code, which I like, but came across an issue. Our clients use Snapshots, so everyday snapshots are generated automatically.  This causes the trigger to fire. Is there anyway to have it not fire on any database with ‘SNAPSHOT’ in the name?

 

Thanks

 


Wednesday, January 16, 2013 - 12:45:13 PM - Gladias Back To Top

Thanks Chad, Good article. I had the similar during my earlier career part managing Oracle DB's but later on we moved away from DDL TTriggers. I read other experts comments as well and kind of tend to agree to some extent. I got the same message "Mail queued" but it did send email to me without any issues or delay. However, I couldn't get the Create Database command you have outlined above. Any suggestions?


Monday, January 14, 2013 - 4:53:25 PM - Jason Back To Top

Thanks Chad,

So how do you handle the problem where you have the trigger pointing to a profile that isn't public, and a user with "DBCreator" rights but no permission to send email?  They suddenly cannot create a new database, they instead get an error "The EXECUTE permission was denied on object 'sp_send_dbmail'..."

What about a successful create.  The output is a helpful "Mail (Id: ...) queued" instead of "Command(s) completed successfully"

I would use either a SQL Agent job to check creation date of all databases, or create a table in a utility database that is managed and populated by a SQL agent job.  Or, if you really, really, really need this near real time, then I would use the Service Broker so your trigger isn't tightly coupled to your email subsystem.


Monday, January 14, 2013 - 3:40:17 PM - Chad Churchwell Back To Top

Jason -

I see your concern although since this trigger is not doing any actions to block the DB creation, I thinkyou would be safe.  The only action is to send an email


Monday, January 14, 2013 - 3:19:47 PM - Sandra Back To Top

I early on saw the need to monitor my SharePoint environment as well.  In my case, I persisted the user DBs in a table and have a job that runs daily comparing this table to sys.databases.  When a new DB is found, it's added to the table and sp_send_dbmail notifies a distribution list.  This doesn't have the immediacy of a trigger but I'm fine with once a day for SharePoint.  And, I also have history of when all the DBs were created.


Monday, January 14, 2013 - 10:56:39 AM - Jason Back To Top

I've thought about implementing something similar.  My concern was if there was something wrong with the trigger or any downstream activities that blocked, the database creation would be blocked as well.  I've never liked using triggers to fire emails.


Learn more about SQL Server tools