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

How notificate create database event Expand / Collapse
Author
Message
Posted Wednesday, March 2, 2011 8:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 1:13 AM
Points: 101, Visits: 517
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.
Post #1071980
Posted Wednesday, March 2, 2011 9:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 25, 2013 7:43 AM
Points: 1,384, Visits: 644
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



Post #1072033
Posted Wednesday, April 13, 2011 1:46 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 10:49 PM
Points: 73, Visits: 320
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
Post #1093145
Posted Thursday, April 14, 2011 5:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 1:13 AM
Points: 101, Visits: 517
Many thanks to all for your help.
Post #1093416
Posted Tuesday, October 18, 2011 7:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 15, 2014 3:22 PM
Points: 1,155, Visits: 547
Is it possible to do something like this for linked servers?
Post #1192016
Posted Tuesday, October 18, 2011 8:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 2,013, Visits: 1,589
Just a thought!

Will it not be good to create a new thread?


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1192119
Posted Wednesday, March 6, 2013 11:04 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 4:41 PM
Points: 1,770, Visits: 3,202
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
Post #1427536
Posted Friday, May 17, 2013 1:36 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:08 PM
Points: 95, Visits: 1,139
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/

Regards
Dirk


--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Post #1453880
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse