Alert Database creation

  • HI Expertz

    Can anyone please tell me how to create an alert (send mail) when a new database is created and also for Backup and Restore operations in your server.

    Tanx 😀

  • You can create an SP using xp_sendmail .Finally create a job that executes daily before you come to office and send you the mail .In xp_sendmail you can use the query :

    select * from sys.databases where datediff(day,create_date ,getdate()) <=1

    For backups you can configure alerts or just add the same step mentioned above with message "backup on server XXXX failed" as a step in case the backup fails in the job .

    Regards

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • Is this possible in express edition.

    Tanx 😀

  • I have never tried creating the database mail profiles and accounts through Express .But for jobs you need SQL Agent .

    Normal Express does nto have express agent with it .

    you need to install advanced tools for SQL Express .After that it should work .I am confident that database mail is suported in Express as well ..

    Regards

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • Database Mail is not supported in the Express Edition, nor is SQL Server Agent.

  • As per this MSDN , yes its not supported .

    http://msdn.microsoft.com/en-us/library/ms165636(SQL.90).aspx

    But there is a tweak 🙂

    Copy DatabaseMail90.exe, DatabaseMailEngine.dll and DatabaseMailProtocols.dll into the MSSQL\Binn directory and execute below mentioned system SPs (in MSDB context)

    dbo.sysmail_start_sp

    dbo.sysmail_stop_sp

    You are done .Actually these 3 files you need to copy from ENT edition or STD edition.

    Regards

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • hi_abhay78 (7/6/2009)


    As per this MSDN , yes its not supported .

    http://msdn.microsoft.com/en-us/library/ms165636(SQL.90).aspx

    But there is a tweak 🙂

    Copy DatabaseMail90.exe, DatabaseMailEngine.dll and DatabaseMailProtocols.dll into the MSSQL\Binn directory and execute below mentioned system SPs (in MSDB context)

    dbo.sysmail_start_sp

    dbo.sysmail_stop_sp

    You are done .Actually these 3 files you need to copy from ENT edition or STD edition.

    Regards

    And probably not legal either unless those files are part of a redistibutable package. Which means if you have any problems you won't get any support from Microsoft.

  • Have you looked at DDL triggers? As far as I know, you can create a trigger that will fire when a DDL event (such as creation of a database) occurs, and have that trigger send the e-mail. That way, you get your notification immediately and don't have to wait until the next morning.

    John

  • only Thing about using triggers, is the fact you can take a hit on the performance. It just depends on the Transaction you are monitoring, something like creating new databases should not happen to often so it may not be a major issue. You might consider using SCOM, SQL DM, or any of the SQL monitoring tools. 🙂

  • John Mitchell-245523 (7/9/2009)


    Have you looked at DDL triggers? As far as I know, you can create a trigger that will fire when a DDL event (such as creation of a database) occurs, and have that trigger send the e-mail. That way, you get your notification immediately and don't have to wait until the next morning.

    John

    This could be a very late reply.

    Sending e-mails from DDL Triggers (or for that reason any trigger) could give some misleading error messages like incorrect "mail profile names" or "Databasemail XP warnings" when creating a new database/table.

Viewing 10 posts - 1 through 9 (of 9 total)

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