MS SQL 2008 - How to implement a “Watch Dog Service” which woofs when too many insert statements on a table

Question

Like my title describes: how can I implement something like a watchdog service in MS SQL 2008 with following tasks: Alerting or making an action when too many inserts are committed on that table.

For instance: Error table gets in normal situation 10 error messages in one second. If more than 100 error messages (100 inserts) in one second then: ALERT!

Would appreciate it if you could help me.

P.S.: No. SQL Jobs are not an option because the watchdog should be live and woof on the fly :-)     

Integration Services? Are there easier ways to implement such a service?

Kind regards,

Sani

Answer 1

Hi Sani,

According to your requirements, it seems that we’d better to add a count table to record the time for every insert operations.
We can use SQL Server triggers to record the time after insert operation in the error table. Some statements as below:

CREATE TRIGGER test
ON errortable
AFTER INSERT
AS 
delete from counttable where timeid<(SELECT GETDATE());
insert into counttable(timeid) values(GETDATE());
GO

We can also make the trigger to counts the records in count table, if it is more than 100 records, using sp_send_dbmail to send you an alert.

References:
http://technet.microsoft.com/en-us/library/ms189799.aspx
http://msdn.microsoft.com/en-us/library/ms190307.aspx

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Iric,

thanks for your answer :)

But isn't there a way to avoid triggers?? Let's assume that we have 500 inserts per second and for each, a trigger is fired which runs some calculations on datetime. Wouldn't that be painful for our database performance?

I mean isn't there a way we can implement a service which monitors those inserts?

Best Regards,

Sani

Answer 3

Sani,

According to my knowledge SQL Server has no service can do this except SQL Server Agent, ie SQL Server jobs.Meanwhile you could provide Microsoft a feature request at https://connect.microsoft.com/SQLServer so that we can try to modify and expand the product features based on your needs.

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

Hi Sani

Depends on how much budgets you have, you might want to look at some third party tools like redgate.

If you want to put the monitoring outside of SQL how about about a scheduled task which fires OSQL commands from batch files.

Regards

Jia