Solved

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

Posted on 2009-03-30
10
580 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
 

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now