Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Replication (2008)
 Alerting on DML operation

Author  Topic 

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2014-09-24 : 11:00:27
Hello experts,

How can we get an alert if one of my tables is getting more than 2000 inserts or 2000 updates or 2000 deletes for 24 hours? please could you suggest how can I setup for this?

can I rely on extended event or any DMV's can help? I'd like to get an alert if more than 2000 records insert / delete / update. is this possible? please suggest.

Cheers,
Vinod Mallolu

Arnav
Even you learn 1%, Learn it with 100% confidence.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-24 : 12:07:11
I don't know of any system tables that would hold this information; perhaps someone else does. Do you have the Insert/Update datetime recorded in the table? If so, you could write a trigger on the table that counted the number of Insert and/or Updates and generate an email or similar alerting mechanism. This won't help with Deletes though. To cover all events, you could write a trigger to log each event and query the number of times each event occurred and alert based on your tolerances. This seems unnecessarily obtuse. What are you actually attempting to accomplish? Perhaps we can suggest a better approach.



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2014-09-24 : 12:31:18
Thank you so much for the reply Bustaz,

recently we have faced an issue in merge replication. because of one table keep on getting more than 2 millions records update a day, replication got troubled a lot. we want to keep track of if any third party sending inserts or updates to our database, we should know that immediately. one stored procedure processed so many updates to our database tables. this is where i'm thinking about setting up some monitoring in place. please suggest.

thanks a lot for responding on this.

Arnav
Even you learn 1%, Learn it with 100% confidence.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-24 : 19:13:08
If it is large insert/update operations that is the suspected cause, you could write an insert/update trigger on the table that would test for the number of records in the "inserted" table. If it is a large number of small events that is the suspected cause, you could write a trigger that would increment a value in a "count table" and test for exceeding the tolerance. I'd hate to think that you needed to do this on a large number of tables.



Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy
Go to Top of Page
   

- Advertisement -