What updates master.sysperfinfo.cntr_value? -- my first EE question ever!

I'd like to use the master..sysperfinfo table for monitoring replication.  I thought I could use Select sysperfinfo.cntr_value
where object_name = 'SQLServer:Replication Agents'
and counter_name = 'Running' and
instance_name = 'Logreader'  
to monitor the number of Logreader agents running (alert when cntr_value < total number of agents).  However, when I check the value (2) it doesn't match the 16 LogReader Agents I see running in EM under Replication Monitor> Agents> Log Reader Agents.

What makes this more puzzling to me is that 2 weeks ago I looked at the cntr_value and it was equal to the number of running Log Reader Agents (16).

Internet and forum searches have not turned up anything that lead to any answers.

Thanks in advance for helping me to understand disparate values between  the cntr_value (2)and actual number of running Log Reader agents.

Gary
Note:  No SQL Server 2000 zone found, using MSSQL2k5

Open in new window

gfahrlanderAsked:
Who is Participating?
 
gfahrlanderAuthor Commented:
Question has been up for 3 weeks tomorrow, I'm closing it with no solution.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
The master.dbo.sysperfinfo system table contains all the SQL Server internal performance counters that are also displayed by PerfMon.

If you configure perfmon again, I believe you will be able to see those counters.

http://www.mssqltips.com/tip.asp?tip=1039
http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
0
 
gfahrlanderAuthor Commented:
Thank you for responding.  I set perfmon as suggested but it only shows the 2 log reader agents that are in the sysperfinfo table and doesn't update the table.
There's got to be something that updates sysperfinfo but a display dependencies shows nothing, no stored procedures, no functions.  I'm down to reading the list of 1,010 stored procedures in the master db to try and find one that updates sysperfinfo.

Gary
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
I hope that spoils your time.. Most of the operations similar to this will be done using DLL's instead of procedures since it is related to Master database.

Normal SP's will be available and accessible for users.
0
 
gfahrlanderAuthor Commented:
Wheee...  Hey, great link to Brent's blog/site though.  Lots of good stuff there, definately a keeper!

Guess I could update master..sysperfinfo myself and see what happens...
0
 
gfahrlanderAuthor Commented:
This looks interesting...  master..sp_MSinit_replication_perfmon
0
 
gfahrlanderAuthor Commented:
Oh joy, a whole bunch of undocumented dbcc commands...
dbcc deleteinstance
dbcc addinstance
dbcc setinstance
dbcc incrementinstance
and a table too.. .  tempdb.dbo.MSreplication_agent_status

Hmmm...

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Have you got it then..

Be careful while dealing with system tables and its values.
Take a backup before doing any changes.
0
 
gfahrlanderAuthor Commented:
No, I haven't got it yet.  I am very hesitant to update system tables or run undocumented system dbcc commands.  I'll hack a registery in a heartbeat but system objects make me a little nervous, especially since it's production.

Gee, my first Experts Exchange question too... I'm also exploring MSDNTechnet for any answers.
0
 
gfahrlanderAuthor Commented:
Update -- solved part of the puzzle, still don't know what updates sysperfinfo though.
tempdb.dbo.MSreplication_agent_status

Publication Type Codes:
0 = Transactional publication
1 = Snapshot publication
2 = Merge publication

Replication Agent Type Codes:
-1 = SQLDMOReplAgent_/Publishers -- Agents supporting publishers
0 = SQLDMOReplAgent_All -- All replication agent types
1 = SQLDMOReplAgent_Snapshot -- Snapshot Agent
2 = SQLDMOReplAgent_LogReader -- Replication transaction Log Reader Agent
3 = SQLDMOReplAgent_/Distribution -- Distribution Agent
4 = SQLDMOReplAgent_Merge -- Merge Agent
5 = SQLDMOReplAgent_/Miscellaneous -- Agents not otherwise classified
9 = SQLDMOReplAgent_/QueueReader -- Replication Queue Reader Agent

Replication Agent Status Codes:
0 = SQLDMOTask_Pending -- All agent-implementing jobs are waiting to start.
1 = SQLDMOTask_Starting -- One or more agent-implementing jobs are starting.
2 = SQLDMOTask_Succeeded -- All agent-implementing jobs have executed successfully.
3 = SQLDMOTask_Running -- At least one agent-implementing job is executing.
4 = SQLDMOTask_Idle -- All agent-implementing jobs are scheduled and idle.
5 = SQLDMOTask_Retry -- At least one agent-implementing job is attempting to execute after a previous failure.
6 = SQLDMOTask_Failed -- At least one agent-implementing job has failed to execute successfully.

References:       http://technet.microsoft.com/en-us/library/ms144002.aspx
      http://technet.microsoft.com/en-us/library/ms186254(SQL.90).aspx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.