January 6, 2011 at 1:35 am
Hello,
On our SQL 2008, we'd like to receive a mail when a DB is created, and, if possible get the name of the newly created database IN the mail.
'till now get get mail I didn't find out how to get the name of the DB in the mail... should I use WMI ?
My database mail is configured and I created a WMI event alert with this query:
SELECT * FROM CREATE_DATABASE
+ Additionnal message in the options of the alert...
But I don't know how to get the name of the newly created db unfortunately.
Any help about that would be pleasant 🙂
Thanks in advance.
Regards.
Franck.
January 6, 2011 at 1:42 am
Schedule a job to send mail to you with the following tsql...it will return with the names of databases created in the last day....
select name from sys.databases
where create_date > (getdate() -1)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 6, 2011 at 1:51 am
Thanks for fast reply !
If I use a job, I'll get a mail every day even if no DB was created, right ?
The purpose is to get a mail only when a DB is created. Because we have a lot of instances and if I set up that kind of system, we'll get 50+ mail everyday for nothing (maybe).
Some of our users have DB_Creator rights but they can stay 1 month without creating any DB :/
Regards.
Franck.
January 6, 2011 at 1:59 am
To slightly change Henricos script to exclude tempdb:
SELECT name FROM sys.databases
WHERE create_date > (GETDATE() -1)
AND name <>'tempdb'
January 6, 2011 at 2:01 am
I understand..the only other way I can think of is Policy Management (SQL 2008 up).
Haven't tried anything like this yet...
Trigger on create?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 6, 2011 at 2:12 am
franck.maton (1/6/2011)
Thanks for fast reply !If I use a job, I'll get a mail every day even if no DB was created, right ?
The purpose is to get a mail only when a DB is created. Because we have a lot of instances and if I set up that kind of system, we'll get 50+ mail everyday for nothing (maybe).
Some of our users have DB_Creator rights but they can stay 1 month without creating any DB :/
Regards.
Franck.
Not necessarily.
If you use an IF EXISTS statement to check if there are new DB's and send the mail based on the result, you'll only get a mail if there's at least one new DB.
January 6, 2011 at 2:25 am
Hi Franck
I've created a CREATE_DATABASE trigger for you. In my example I write the name of the new database + the time it was created into a table and database I created for testing purposes. You can easily change that to sending a mail instead though.
CREATE TRIGGER newDatabaseTrig
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @database sysname
SET @database = (SELECT TOP 1 name FROM sys.databases ORDER BY create_date DESC)
INSERT INTO Newdbtest.dbo.newdbtable VALUES(@database, GETDATE())
I hope you'll find it useful.
Best regards
Lars Mikkelsen
January 6, 2011 at 4:50 am
Thanks to all for your help 🙂
January 10, 2011 at 2:40 am
Hum,
Here's the code I tested:
create trigger [newdbtrg]
on ALL Server
for CREATE_DATABASE
EXEC msdb.dbo.sp_send_dbmail
@recipients='DBA@company.com',
@subject='A new database was just created !',
@body='A new database was created. Database name: ',
@query = 'SELECT TOP 1 name FROM master.sys.databases WHERE name <>''tempdb'' ORDER BY create_date DESC';
And, erm, it doesn't works. I tried to create a DB to test the trigger and, and my process hangs on DB creation, unfortunately. I read I had to use XML eventdata streams to catch the newly created DB, can somebody confirm ?
Thanks in advance.
Regards.
Franck.
January 10, 2011 at 3:33 am
franck.maton (1/10/2011)
Hum,And, erm, it doesn't works. I tried to create a DB to test the trigger and, and my process hangs on DB creation, unfortunately. I read I had to use XML eventdata streams to catch the newly created DB, can somebody confirm ?
Thanks in advance.
Regards.
Franck.
Hi again Franck
I didn't test my trigger when used in combination with mail. When inserting a row into a database it works fine though.
My suggestion therefore is this:
1. Create a database called mgmtdb.
2. Create a table in that database like this:
CREATE TABLE [dblist]
(
[name] sysname,
[createdate] datetime,
[mail_sent] int
)
3. Then create a trigger like this:
-- The trigger is executed when a new db is created.
-- If the name of the new database is different from tempdb,
-- the name is written to the dblist
CREATE TRIGGER [newdbtrig]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @database sysname
SET @database = (SELECT TOP 1 [name] FROM sys.databases ORDER BY [create_date] DESC)
IF (@database != 'tempdb')
BEGIN
INSERT INTO [mgmtdb].[dbo].[dblist] VALUES(@database, GETDATE(), 0)
END
4. And schedule a job like this:
-- Suggestion for a job to run every 10 minutes or so
-- The query checks if any databases in the dblist has mail_sent = 0
-- If that is the case a mail is sent
IF EXISTS (SELECT 1 FROM [mgmtdb].[dbo].[dblist] WHERE [mail_sent] = 0)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@recipients='DBA@company.com',
@subject='New database(s) was just created!',
@body='Database name(s): ',
@query = 'SELECT name FROM [mgmtdb].[dbo].[dblist]';
UPDATE [mgmtdb].[dbo].[dblist] SET [mail_sent] = 1
END
The trigger inserts any new databases not named tempdb into the dblist table of the mgmt database. The job sends a mail if any databases in the dblist table has mail_sent = 0 and updates the values to 1 when the mail is sent.
Hope you can use it this time. If not please reply again.
Best regards
Lars Søe Mikkelsen
January 10, 2011 at 8:19 am
Thanks for your reply Lars. I'll try your solution tomorrow and I'll give you feedback.
Have a nice day.
Regards.
Franck.
January 10, 2011 at 11:30 am
this will work for you;
CREATE TRIGGER newDatabaseTrig
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @database sysname
SET @database = (SELECT TOP 1 name FROM sys.databases where name != 'tempdb' ORDER BY create_date DESC)
exec msdb.dbo.sp_send_dbmail
@recipients='your@email.com',
@subject='A new database was just created !',
@body= @database
January 10, 2011 at 12:45 pm
An alternate solution would be to have a server trigger generate a Service Broker item, and then have the recieving queue do a send_db_mail. Or just have the trigger itself do it, kind of like what Lars mentioned above, just skip the midpoint table.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 11, 2011 at 12:31 am
Since yesterday I tried different solutions to solve my problem. The solution of Lars works, indeed.
Here's another one that works too:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [newdbtrg]
on ALL Server
for CREATE_DATABASE
as
set nocount on
declare @data xml
declare @message varchar(1000)
declare @instance_name varchar(30)
declare @sujet varchar(150)
set @data = EVENTDATA()
select @instance_name = @@ServerName
SET @message = 'New DB: ' + @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)') + ' created.'
SET @sujet = 'NEW DATABASE CREATED ON INSTANCE ' + @instance_name
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'you@company.com',
@body = @message,
@subject = @sujet ;
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
Geoff A, your solution works aswell 🙂
Thank you all for your help.
Have a nice day.
Franck.
February 27, 2015 at 8:15 am
Thanks for this. This is exactly what I was looking for. One question though. Where does it create this trigger? I looked at the master database under database triggers and it wasn't there.
Thanks,
Jim
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply