2

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?

3
  • Thomas has answered your question. This will help you out as well .. sqlblog.org/2011/05/03/…
    – Kin Shah
    Commented Jul 15, 2013 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. Commented Jul 15, 2013 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
    Commented Jul 16, 2013 at 13:53

2 Answers 2

3

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.

0
0

I suggest you the script you can find on this page.

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Unrestricted -Force

$ErrorActionPreference = "Stop"
$VerbosePreference = "Continue"

cls

Write-Output "Collecting counters..."
Write-Output "Press Ctrl+C to exit."

$counters = @("\Processor(_Total)\% Processor Time", 
"\LogicalDisk(_Total)\Disk Reads/sec", 
"\LogicalDisk(_Total)\Disk Writes/sec", 
"\SQLServer:Databases(_Total)\Log Bytes Flushed/sec") 

Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 3600 | 
    Export-Counter -FileFormat csv -Path "C:\sql-perfmon-log.csv" -Force

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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