Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-07-11
6
Medium Priority
?
1,149 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 43

Accepted Solution

by:
Eugene Z earned 2000 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 2000 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

963 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