Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying create a PS script to read some performance counter about my SQL for my Level 1 partners but I'm having a problem with the instance name.

I'm using this code:

$computer = "\\\" + $ENV:Computername
$instance = $computer + '\MSSQL$SG25DB'
write-Host $instance
Write-Output -InputObject "Use of Memory Buffer. The expected value must be greater than 80."
Get-Counter "$instance:Buffer Manager\Buffer cache hit ratio"

and the output is:

\\[hostname]\MSSQL$SG25DB <--- Here appears the proper value :) Use of Memory Buffer. The expected value must be greater than 80. Get-Counter : The specified counter path could not be interpreted. At line:5 char:12 + Get-Counter <<<< "$instance:Buffer Manager\Buffer cache hit ratio" + CategoryInfo : InvalidResult: (:) [Get-Counter], Exception + FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

I have verified with Write-Host that the value of variable $instance is properly constructed but when I apply it to the Get-Counter command its fails. Do you have any idea? Could be related with the special character $? How can I make run it properly?

share|improve this question
    
Thomas has answered your question. This will help you out as well .. sqlblog.com/blogs/aaron_bertrand/archive/2011/05/03/… –  Kin Jul 15 '13 at 17:23
    
@Kin The problem the OP is having isn't with the dollar sign interpretation in the instance name. The OP correctly uses the literal string annotations with the single quotes. That propagates over to the variable's reference in Get-Counter. Two separate problems as far as I can see. –  Thomas Stringer Jul 15 '13 at 17:36
    
Hi Thomas, I have tested your suggestion. As you say with write-Host "$($instance):Buffer Manager\Buffer cache hit ratio" the value shown is the proper, but when I use this chain (Get-Counter "$($instance):Buffer Manager\Buffer cache hit ratio") the same problem appears: Get-Counter : The specified object was not found on the computer. At line:6 char:12 + Get-Counter <<<< "$($instance):Buffer Manager\Buffer cache hit ratio" + CategoryInfo : InvalidResult: (:) [Get-Counter], Exception + FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand –  Simkin Jul 16 '13 at 13:53
add comment

1 Answer 1

Try this:

Get-Counter "$($instance):Buffer Manager\Buffer cache hit ratio"

What happens is that your $instance variable isn't isolated, so therefore you need to explicitly state what the variable is and where it ends. You can do that with the above notation (dollar sign and parenthesis around the variable name).

Take a look at the different outputs of the following:

Write-Host "$instance:Buffer Manager\Buffer cache hit ratio"

The output is:

 Manager\Buffer cache hit ratio

Now with this correct notation:

Write-Host "$($instance):Buffer Manager\Buffer cache hit ratio"

The output is this:

\\MyMachineName\MSSQL$InstanceName:Buffer Manager\Buffer cache hit ratio

A common way to troubleshoot these sorts of problems is to use the above methodology, by seeing exactly what you're passing into other cmdlets/functions/etc.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.