SQL Monitoring

Question

Viewing 6 posts - 1 through 5 (of 5 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You could create a DDL trigger for CREATE_DATABASE events and send a db mail with code similar to this:

CREATE TRIGGER databaseCreated

ON ALL SERVER

FOR CREATE_DATABASE

AS

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'profile',

@recipients = 'somebody@mycompany.com',

@subject = 'New Database',

@body = 'New database created!'

END

Hope this helps

Gianluca

-- Gianluca Sartori

waseem.shahzad 45937 SSC Eights! Points: 853 More actions · Answer 2

Thanks

What is meant by "profile'

@profile_name = 'profile',

Secondry ; Did i need to run this script everytime whenever i need to know or run once?

I just want to set SQL Server as whenever there would be a change in anythings like Addition of New DB ; Let me know by Email.

Thanks in advance

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

What I posted is a DDL trigger, which is a trigger that captures DDL changes instead of data changes.

Whenever a new database is created, the trigger will fire and run the code inside it.

The code block inside the trigger sends a e-mail using the database mail feature. "Profile" is the profile name you have to set up in database mail.

-- Gianluca Sartori

waseem.shahzad 45937 SSC Eights! Points: 853 More actions · Answer 4

After executing this trigger on the server.

I am unable to add any database unless i run:-

drop trigger databaseCreated on all server

WHY?

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Probably because you didn't set up database mail correctly and the trigger fails to execute.

-- Gianluca Sartori