Monitoring table size

We have a table whose size we wish to keep below 200 records. My thought is
to run a query every minute via SQL agent to get the record count. What I am
wondering is if it is possible to log the result of the query as a perfmon
counter so that we can both graph it realtime in perfmon and also alert off
it in Microsoft Operations Manager.
Thanks,
Mark
"Mark Murphy" <viosk@.newsgroup.nospam> schrieb im Newsbeitrag
news:7AE5DEC0-1F6C-4D60-AC99-FB50A13EFAD9@.microsoft.com...
> We have a table whose size we wish to keep below 200 records. My
thought is
> to run a query every minute via SQL agent to get the record count. What
I am
> wondering is if it is possible to log the result of the query as a
perfmon
> counter so that we can both graph it realtime in perfmon and also alert
off
> it in Microsoft Operations Manager.
And how will you react on this? If there's some action that lends itself
to automation, then a trigger is probably the most appropriate means.
That way you reduce load on the db and do the checks only when they are
necessary (i.e. on insertion).
Kind regards
robert
|||Hi Mark
In SQL Server 2000, there are user defined counters that can log any value
you send as a parameter. For example, if you had this statement: EXEC
sp_user_counter1 10, you could watch SQL Server User Counter 1 in a perfmon
graph, and see the value 10.
But you have to explicitly call the procedure to set the value, and you
could do that in a trigger every time the data in the table changes. The
trigger would do a select count(*), save the result into a variable, and
pass the variable to the sp_user_counter procedure. The trigger could also
check for the value being over your limit, and take the alerting action,
rather than using SQL Server's alert mechanism.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mark Murphy" <viosk@.newsgroup.nospam> wrote in message
news:7AE5DEC0-1F6C-4D60-AC99-FB50A13EFAD9@.microsoft.com...
> We have a table whose size we wish to keep below 200 records. My thought
> is
> to run a query every minute via SQL agent to get the record count. What I
> am
> wondering is if it is possible to log the result of the query as a perfmon
> counter so that we can both graph it realtime in perfmon and also alert
> off
> it in Microsoft Operations Manager.
> Thanks,
> Mark
|||Thanks Kalen and Robert,
Exactly the advice I was looking for. We have a sproc that needs to poll
the table for new records periodically (We know it's less efficient, but it's
beyond our control). I'll set the counter there.
-Mark