Alert Database creation

Question

Alert Database creation

Eswin

SSCrazy Eights

Points: 8320
More actions
June 28, 2009 at 11:08 pm

#223092

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 😀

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

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

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 1

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)

Eswin SSCrazy Eights Points: 8320 More actions · Answer 2

Eswin

SSCrazy Eights

Points: 8320

June 29, 2009 at 12:03 am

#1016616

Is this possible in express edition.

Tanx 😀

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 3

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 5

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

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

Kevi322000 Right there with Babe Points: 746 More actions · Answer 8

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

Mohammed Imran Ali SSCrazy Points: 2125 More actions · Answer 9

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.