March 2, 2011 at 8:32 am
Hi,
I need to detect when a new database is created in my SQL Server and send a mail notification to all dba-s.
My idea is to do that with notification services: first i capture the create database event, second i enquee message in the queue and finally i process this queue sending notification mails to all dba-s.
Someone could tell me if it is possible? How I can read the messages from a queue to send notifications by mail? How i can see the queue content?
Many thanks in advance.
March 2, 2011 at 9:43 am
depending on your version of sql2008 there is a audit function available to do that
there is also a trigger that will do that too
April 13, 2011 at 1:46 pm
Here is a Trigger that I created and currently use on SQL 2005 and 2008.
It monitors when someone creates a new DB and emails me the information, including the create statement and the user.
Note: it will not work on earlier versions of SQL.
CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @bd varchar(max)
Declare @tsql varchar(max)
Set @tsql = EVENTDATA().value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')
SET @bd = 'UserName: ' + UPPER(SUSER_NAME()) + '
ServerName: ' + @@SERVERNAME + '
Time: '+ CONVERT(varchar(25),Getdate()) + '
HostName: ' + HOST_NAME() + '
Database: ' + db_name() + '
T-SQL: ' + @tsql
BEGIN
PRINT 'Make sure you have informed all DBAs before creating databases. This event has been logged'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Database Mail',
@recipients = 'myEmail@myDomain.com',
@subject = 'A new database has been created!',
@body_format = 'HTML',
@importance = 'High',
@body = @bd
END
GO
ENABLE TRIGGER [DDL_CREATE_DATABASE_EVENT] ON ALL SERVER
GO
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
April 14, 2011 at 5:36 am
Many thanks to all for your help.
October 18, 2011 at 7:20 am
Is it possible to do something like this for linked servers?
October 18, 2011 at 8:25 am
Just a thought!
Will it not be good to create a new thread?
March 6, 2013 at 11:04 am
If some user restore database from a copy of backup and give a new name, this is also considered as a new database, then the stored procedure won't notify that.
how can we deal this situation?
Thanks
May 17, 2013 at 1:36 am
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply