Solved

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

Posted on 2009-03-30
10
578 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

758 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

23 Experts available now in Live!

Get 1:1 Help Now