Monitoring Issues

Question

Monitoring Issues

mr_primate

Valued Member

Points: 54
More actions
December 18, 2008 at 1:56 pm

#204210

Hi, i´m working on sql server 2005, monitoring a data base. I have the following task: to create an alarm system by mail, of events like process that take more than 3 seg and the sql server profiler detects.
I´m saving the monitoring data in a table of the data base. I also have a trigger that sends the email.
The issue ocurrs when the trace starts, and it seems that all the triggers are deleted, all that are related to the table where i save the data of the trace. If I create the trigger while the trace is working, it stops and sends an error.
what do you recommend to do?

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

I would honestly recommend that you figure out another way to monitor this. What you have is an incredibly expensive way of doing this, and I wouldn't do it on any of my production servers.

to solve your current issue, you probably need to create a DDL Trigger that will catch the DROP of the TRIGGER or the DROP/CREATE of the table and will run an dynamic SQL Script to recreate your triggers.

Conan Whalen-McKain-370433 Ten Centuries Points: 1154 More actions · Answer 2

Conan Whalen-McKain-370433

Ten Centuries

Points: 1154

December 18, 2008 at 2:18 pm

#914872

Do you have profiler running all the time??

mr_primate Valued Member Points: 54 More actions · Answer 3

In this moment I don`t have profiler running all time, but my task is create an alarm system running all time...

I need an alternative to resolve this task.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

I would write a .NET application that reads the SMO feed of a trace and watches for the thresholds to be passed, or upgrade to SQL Server 2008 and use Extended Events do monitor this. Logging the trace to a table is much more expensive than looking at the memory feed from SMO and finding key events to trigger emails in a multi-threaded service. SQL Trace will get you the information just fine in 2000 and 2005, but you need an application to efficiently manage that data feed coming from the server.

mr_primate Valued Member Points: 54 More actions · Answer 5

mr_primate

Valued Member

Points: 54

December 22, 2008 at 7:37 am

#916137

Thanks you for your answer, i'll do what yo say