Solved

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

Posted on 2009-03-30
10
585 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:gfahrlander
  • 7
  • 3
10 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24025676
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
 

Author Comment

by:gfahrlander
ID: 24029543
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24029652
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:gfahrlander
ID: 24029740
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
 

Author Comment

by:gfahrlander
ID: 24029780
This looks interesting...  master..sp_MSinit_replication_perfmon
0
 

Author Comment

by:gfahrlander
ID: 24029914
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24030170
Have you got it then..

Be careful while dealing with system tables and its values.
Take a backup before doing any changes.
0
 

Author Comment

by:gfahrlander
ID: 24030245
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
 

Author Comment

by:gfahrlander
ID: 24032636
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
 

Accepted Solution

by:
gfahrlander earned 0 total points
ID: 24132969
Question has been up for 3 weeks tomorrow, I'm closing it with no solution.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question