June 28, 2009 at 11:08 pm
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 😀
June 28, 2009 at 11:51 pm
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)
June 29, 2009 at 12:03 am
Is this possible in express edition.
Tanx 😀
June 29, 2009 at 12:12 am
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)
July 6, 2009 at 8:30 pm
Database Mail is not supported in the Express Edition, nor is SQL Server Agent.
July 6, 2009 at 9:31 pm
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)
July 6, 2009 at 10:09 pm
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.
July 9, 2009 at 4:39 am
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
February 14, 2012 at 7:37 am
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. 🙂
May 8, 2013 at 10:07 pm
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