Solved

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

Posted on 2006-07-11
6
1,116 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
  • 3
  • 3
6 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ 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 42

Assisted Solution

by:EugeneZ
EugeneZ 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 42

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

820 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