Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Alert Database creation Expand / Collapse
Author
Message
Posted Sunday, June 28, 2009 11:08 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:50 AM
Points: 1,607, Visits: 1,039
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
Post #743354
Posted Sunday, June 28, 2009 11:51 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 13, 2014 12:27 PM
Points: 267, Visits: 386
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)
Post #743367
Posted Monday, June 29, 2009 12:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:50 AM
Points: 1,607, Visits: 1,039
Is this possible in express edition.



Tanx
Post #743373
Posted Monday, June 29, 2009 12:12 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 13, 2014 12:27 PM
Points: 267, Visits: 386
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)
Post #743375
Posted Monday, July 6, 2009 8:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 23,240, Visits: 31,937
Database Mail is not supported in the Express Edition, nor is SQL Server Agent.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #748244
Posted Monday, July 6, 2009 9:31 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 13, 2014 12:27 PM
Points: 267, Visits: 386
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)
Post #748270
Posted Monday, July 6, 2009 10:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 23,240, Visits: 31,937
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #748291
Posted Thursday, July 9, 2009 4:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 5,308, Visits: 9,700
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
Post #750058
Posted Tuesday, February 14, 2012 7:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 8, 2013 12:56 PM
Points: 428, Visits: 136
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.
Post #1251909
Posted Wednesday, May 8, 2013 10:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 12, 2014 1:09 AM
Points: 72, Visits: 251
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.
Post #1450876
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse