By: Chad Churchwell | Updated: 2013-01-14 | Comments (21) | Related: > 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.
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
About the author
Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2013-01-14
