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.
| 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 MalloluArnavEven 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 |
 |
|
|
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.ArnavEven you learn 1%, Learn it with 100% confidence. |
|
|
|
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 |
|
|
|
|
|
|
Alerting on DML operation