how to raise alert on table row count

Question

how to raise alert on table row count

mickeytech2000

Mr or Mrs. 500

Points: 556
More actions
September 29, 2010 at 5:47 pm

#224565

I need to raise an alert when a table row count exceeds a certain value. Please help me and let me know how to do that. Thank you very much in advance.
Mickey

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

mickeytech2000 (9/29/2010)
I need to raise an alert when a table row count exceeds a certain value. Please help me and let me know how to do that. Thank you very much in advance.
Mickey

You'll either need to add it to the code where you do the inserts (ie: SELECT COUNT(*) or SELECT from sys.partitions (look that up in Books Online), or you'll need to write a trigger (also in Books Online).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mickeytech2000 Mr or Mrs. 500 Points: 556 More actions · Answer 2

I am not doing any Inserts. All I need is when the number of rows in a table reaches 50, I want an alert raised and written to the Windows log or emailed out directly using xp_sendmail.

Any sample code would be greatly appreciated.

Thanks again.

Mickey

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

mickeytech2000 (9/30/2010)
I am not doing any Inserts. All I need is when the number of rows in a table reaches 50, I want an alert raised and written to the Windows log or emailed out directly using xp_sendmail.
Any sample code would be greatly appreciated.
Thanks again.
Mickey

Um... errr... you confused me on this one. If you're not doing inserts how is the table getting more rows?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mickeytech2000 Mr or Mrs. 500 Points: 556 More actions · Answer 4

The application is doing the inserts and I am not changing the application code. This table is a document processing queue. I just want to monitor the queue. If it reaches 50, I want to send out an alert.

Thanks again for any code sample as how to accomplish this.

Mickey

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

You could write a trigger to do this, or you could write a job that ran every minute or so and checked the table.

I'd lean towards the latter. If you have a trigger, and people are adding data in there, you could end up with tons of alerts going out, which might not be what you want.

What I'd suggest is that you make a table to store the alert. Something simple like a 1 row table, with LastAlert, RowCount, active as columns.

Then, I'd write a stored procedure that did a SELECT COUNT(*) from the table and if the value was > 50, do this

- check your alert table.

- if the last alert was within xxx minutes and active = 1, perhaps do nothing. This is to prevent cascading.

- If active = 0, do nothing

- Otherwise, raise the alert. You can use RAISERROR to do this, or you can send a mail to the appropriate group

- update the table with the last rowcount and alert timestamp.

Schedule this proc to run every minute.

If you can acknowledge the alert, or are working on it, you can set Active to 0 for a period of time. When you're ready, set back to 1.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Steve Jones - Editor (10/1/2010)
You could write a trigger to do this, or you could write a job that ran every minute or so and checked the table.
I'd lean towards the latter. If you have a trigger, and people are adding data in there, you could end up with tons of alerts going out, which might not be what you want.
What I'd suggest is that you make a table to store the alert. Something simple like a 1 row table, with LastAlert, RowCount, active as columns.
Then, I'd write a stored procedure that did a SELECT COUNT(*) from the table and if the value was > 50, do this
- check your alert table.
- if the last alert was within xxx minutes and active = 1, perhaps do nothing. This is to prevent cascading.
- If active = 0, do nothing
- Otherwise, raise the alert. You can use RAISERROR to do this, or you can send a mail to the appropriate group
- update the table with the last rowcount and alert timestamp.
Schedule this proc to run every minute.
If you can acknowledge the alert, or are working on it, you can set Active to 0 for a period of time. When you're ready, set back to 1.

Good to see that even with all you do that you still have the time to keep your hand in it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)