Implementing a watchdog service for MS SQL 2008 R2 - Counting table inserts per second

Question

Implementing a watchdog service for MS SQL 2008 R2 - Counting table inserts per second

sanismalls

Old Hand

Points: 361
More actions
July 12, 2012 at 6:03 am

#261101

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

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I think you'd be best off building a custom service for that. Should be simple enough to build a .NET service that you grant access to the tables and datbases you want to monitor.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

OK, what about this? An external stored procedure call using batch file scripting and a SQL input file.

Set up your table object:

CREATE TABLE CountTable (

uid INT IDENTITY(1,1) PRIMARY KEY NOT NULL,

theCount BIGINT NOT NULL )

INSERT INTO CountTable (theCount)

SELECT COUNT(*) FROM TargetTable;

Write a batch file: go.bat

SQLCMD -Slocalhost -E -iCheckCount.sql

CheckCount.sql

DECLARE @lastCount BIGINT,

@thisCount BIGINT,

@recordsInserted BIGINT;

SET @lastCount = ( SELECT theCount FROM countTable WHERE uid = ( SELECT MAX(uid) FROM countTable ) );

SET @thisCount = ( SELECT COUNT(*) FROM TargetTable );

SET @recordsInserted = ( @thisCount - @lastCount );

IF @recordsInserted IS NOT NULL AND @recordsInserted > 10

RAISERROR('Too many records inserted!',16,1) WITH LOG;

INSERT INTO CountTable (theCount)

VALUES (@thisCount)

Then set up a Scheduled Task in Windows to run 'go.bat' every, e.g. 1 minute. Should the number of entries in your error log table (referenced above as 'TargetTable') exceed your threshold value (referenced above as 10) then an error will be fired to the error log. You can then use ordinary alert notifications to get notified about this.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Dwain Camps SSC Guru Points: 86978 More actions · Answer 3

Derek - Why are you recommending to use a Windows Scheduled task?

Seems like a SQL Server Agent job running the exact same SP would work. You can then set the Agent to detect the RAISEERROR and send an email or do some other action?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

sanismalls Old Hand Points: 361 More actions · Answer 4

Thanks for your quick replies.

One question: what is the best way to get the rows inserted in the last 10 seconds for example.

Let's assume that I have a table ERROR with just one column TIME where datetimes are inserted.

How can I calculate the inserts from the last 10 seconds??

Like: select count(*) from ERROR where TIME ....

There are a lot of ways to do it. But what is the best practice?

Best regards,

Sani

sanismalls Old Hand Points: 361 More actions · Answer 5

Actually I'm doing it like this:

insert into ERROR values (GETDATE())

select count(*)

from ERROR

WHERE errorcolumn

BETWEEN dateadd(ss,-30,getdate()) AND GETDATE()

order by error