Solved

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

Posted on 2006-07-11
6
1,106 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

920 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

15 Experts available now in Live!

Get 1:1 Help Now