How notificate create database event

  • 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.

  • 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

  • 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

  • Many thanks to all for your help.

  • Is it possible to do something like this for linked servers?

  • Just a thought!

    Will it not be good to create a new thread?

  • 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

  • Hi,

    you can use SQL Server Agent Alerts which can notify you about Restores.

    Take a look here:

    http://www.sqlservercentral.com/articles/Administration/alertalertbackupandrestorebaby/1253/[/url]

    Regards

    Dirk

    --
    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply