Solved

Named instances not showing in Perfmon - SQL2K-sp3 running on Win 2K Advanced Server

Posted on 2006-07-11
6
1,123 Views
Last Modified: 2012-06-27
The SQL server has three instances:
the default instance (server1) on port 1433 - which is not used in production,
named instance 1 (server1\nc_gp) on port 1443 - heavily used, and
named instance 2 (server1\nc_qc) on port 1533 - moderately used.  
In perfmon I see only "\\server1" in the "Select counters from computer" drop down.  When I add the Performance object [SQLServer: General Statistics - User Connections] it shows 2 connections, which is what is coming from the default instance.  The named instance "server1\nc_gp" has >100 connections according to sp_who2 and SEM.  I've tried typing "server1\nc_gp" and "\\server1\nc_gp" in the "Select counters from computer" drop down and then clicking to Add the desired performance metric, but it throws an "unable to connect to machine" error.  The "Select instances from list/All instances" radio buttons are greyed out.  Beating my head against the wall for hours on this.  Ideas?
0
Comment
Question by:dmeenan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 43

Accepted Solution

by:
Eugene Z earned 500 total points
ID: 17085891

You may  try running the following commands from command prompt to reload the Perf counters.

unlodctr.exe MSSQLServer <$instance name>

lodctr.exe SQLCTR.INI  <sql>\BINN\sqlctr.ini (give full path of these files on your machine)

Restart the machine
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 500 total points
ID: 17086112
also check:
PRB: Performance Object Is Not Displayed in Performance Monitor
http://support.microsoft.com/default.aspx?scid=kb;EN-US;248993
0
 

Author Comment

by:dmeenan
ID: 17089942
I'm not missing any perfmon metrics, I just don't have the ability to monitor SQL named instances.  I've checked on some of our other SQL servers (I've got 30 named instances on various boxes) and none appear to allow me to select an instance.  I tried the reg hack for Disable Performance Counters reg keys to no avail and I've tried bouncing the server.  KB227662 had some pointers to try, but they all address missing performance counters - which isn't applicable, as I understand the problem.  I've tried everything I can find online to figure out how to monitor a named instance, but no luck so far.  Thanks for the advice though.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:dmeenan
ID: 17093047
I stand corrected.  I've sampled various servers and found that I am, in fact, missing the Perfmon counters for only some of the named instances.  I thought you selected the named instance from the "select instances" text box, but I should be looking for the named instances in the Performance object selection box.  What I am seeing are the default instance counters and not seeing the named instance counters (of the form "MSSQL$<named instance>: General Statictics", e.g.).  I can now see that I am missing counters.  I've looked at diskperf and it said that performance counters are set to start at boot, so I must conclude that there is a SQL server instance installation issue or instance configuration issue.  I'll revisit KB248993 and see if that is the answer.
0
 

Author Comment

by:dmeenan
ID: 17093529
SOLVED!   KB227662 had the clue, specifically for SQL 6.5, but it was the right idea --> "Make sure that the system path variable includes the path to the Sqlctr60.dll file."  I found the Sqlctr80.dlls existed in each instance's BINN directory, so on a whim I added one named instance's BINN path to the server Path statement as shown.  I inserted "C:\Program Files\Microsoft SQL Server\MSSQL$NC_GP\BINN;" into the Path statement shown in #1, to get the Path statement in #2, and Voila! The Performance object dropdown immediately showed the counters MSSQL$NC_GP:, etc, etc, etc.  NO REBOOT REQUIRED!  Yee-haw!  

#1. %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Microsoft SQL Server\80\Tools\BINN;C:\WINNT\Microsoft.NET\Framework\v1.0.3705

#2. %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Microsoft SQL Server\80\Tools\BINN;C:\Program Files\Microsoft SQL Server\MSSQL$NC_GP\BINN;C:\WINNT\Microsoft.NET\Framework\v1.0.3705

Although EugeneZ didn't give me the specific answer, I feel his suggestions gave me the hints and guidance to solve this, ergo, points to EugeneZ.  Thanks so much!
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 17094615
dmeenan,
Good team work.
Thanks ;)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL remove duplicates from different columns 14 52
Server 2012 r2 and SQL 2014 6 34
When are cursors useful? 8 62
Section based report in SSRS 14 35
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

751 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