SQL "Log Bytes Flushed/sec" counter from powershell Error

0
votes

I am trying to calculate the type of AZURE SQL I need using this: http://dtucalculator.azurewebsites.net/ => http://dtucalculator.azurewebsites.net/Downloads/sql-perfmon.zip

This is a PowerShell script that gets data from some counters from SQL but it errors out when accessing the "Log Bytes Flushed/sec" counter that it is pressent in SQL when I run

select *  FROM sys.dm_os_performance_counters where counter_name like 'Log Bytes Flushed/sec%'

the error I get from PowerShell is:

Get-Counter : The specified object was not found on the computer. At C:\users\ringhel\desktop\sql-perfmon.ps1:47 char:1 + Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 3600 | + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidResult: (:) [Get-Counter], Exception + FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

The error comes from this variable:

$counters = @("\Processor(_Total)\% Processor Time", "\LogicalDisk(C:)\Disk Reads/sec", "\LogicalDisk(C:)\Disk Writes/sec",

"\LogicalDisk(C:)\Disk Read Bytes/sec", "\LogicalDisk(C:)\Disk Write Bytes/sec", "\SQLSERVER:Databases($DatabaseName)\Log Bytes Flushed/sec")

After searching the web, instead of SQLSERVER I tried MSSQLSERVER (this is the instance name of the SQL server), MSSQL'$MSSQLSERVER and MSSQL$MSSQLSERVER but the same error.

Please help.

2
powershellazureazure-sql-databasecounterazure-performancecounters
Start Perfmon on the server and find the counter you want and you'll see the name, instance name, etc... – Tony Hinkle
I started perfmon on the server, that is how I got the error saying that the counter does not exist even though the counter exists in sql as I pointed out in the description. – Mihai Darzan
i found the counter in perfmon, but I still get the same error, even thought the counter exists – Mihai Darzan

2 Answers

1
votes

found the problem. I had to set the execution policy for the LocalMachine to RemoteSigned. It was Undefined.

Set-ExecutionPolicy RemoteSigned -Scope LocalMachine

0
votes

My issue was slightly different. For a named instance I had to define the SQL Server counter like this:

\MSSQL`$SQL2016:Databases(_Total)\Log Bytes Flushed/sec

My instance was named "SQL2016". The back tick escapes the $ in the instance name.