Monitoring Updates to tables

Question

Post reply

Monitoring Updates to tables

Bill-139371

SSC Enthusiast

Points: 143
More actions
April 26, 2005 at 2:26 pm

#87304

My first post here.
I been asked to come up with ideas for monitoring critical updates to SQL tables on diffrent servers and if the do not get updated email certain users from a list maintained in another table.
I'm mainly a VB and VB.Net coder so it would be easy fro me to code a program with a timer that checks the tables or a local service and runs on a local client.
My question is is there a better way within SQL Server maybe to do something like this on a server.
Create a agent or service on the server which is more reliable and up 24/7. I'm open for any ideas
Thanks,
Bill Gardner

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

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

How do you define whether your table is not updated in your situation? Maybe you can show some examples?

Rob Alexander SSC-Addicted Points: 484 More actions · Answer 2

Are the tables that you want to monitor high-traffic tables? In other words, is the number of transactions run on the tables that you want to track high (thousands/minute? hundreds/minute? or what?)?

You could track changes using table triggers. However, doing so could have a slightly negative impact on system performance.

Bill-139371 SSC Enthusiast Points: 143 More actions · Answer 3

Thanks for the responses.

Currently I have 2 tables on 2 different servers that need to be checked and emails to go out from a list in a table if there is a problem. They are not high traffic tables.

On one server if a tables record count ever reaches a certain number an alert need to be emailed. Records in this table are being inserted and deleted by 2 another process on other servers and should the record count ever get above, say, 100 email alerts need to go out to the person listed as the process that takes those records one-by-one, does it thing and then removes the record is down.

On another server, the condition is that; at a certain time, around 8:00 EST, this SQL statements return should have nulls in certain columns otherwise a scheduled process did not kick off and run on the server:

The SQL Statement

SELECT TOP 1 * FROM BATS..LastDataLoad ORDER BY DataLoadID DESC

I would also like it if this monitor could possible alert check for a downed server as these are not on UPS in the main server room.

Hope this makes sense.

In explaining this I do wonder if a single monitor program/agent/service may not be possible, but hey, I am no expert.

Thanks,

Bill

Rob Alexander SSC-Addicted Points: 484 More actions · Answer 4

My suggestion is to create two DTS packages on the main server.

The first package would check the record count and send an emaill if the record count exceeded whatever limit you want to set. Then I would schedule this DTS package to execute at a regular interval.

The second package would check the column content as you described and send an email if the situation warranted. I would schedule this DTS package to execute once a day.

As for the up-time of the servers not in the main server room, I guess you could create a job on the main server that tries to open a connection on the servers that are not the server room. If the job fails, then have it raise an alert.

Bill-139371 SSC Enthusiast Points: 143 More actions · Answer 5

Excellent. Yes this does appear to be what I needed. Thanks. I'm off to learn a little about DTS.