Solved

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

Posted on 2006-07-11
6
1,102 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

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!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 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

20 Experts available now in Live!

Get 1:1 Help Now