• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 608
  • Last Modified:

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.

Note:  No SQL Server 2000 zone found, using MSSQL2k5

Open in new window

  • 7
  • 3
1 Solution
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.

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.

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.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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...
gfahrlanderAuthor Commented:
This looks interesting...  master..sp_MSinit_replication_perfmon
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


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.
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.
gfahrlanderAuthor Commented:
Update -- solved part of the puzzle, still don't know what updates sysperfinfo though.

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
gfahrlanderAuthor Commented:
Question has been up for 3 weeks tomorrow, I'm closing it with no solution.
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now