Search code examples
wmisql-agent

WMI SQLSERVERAGENT_SQLAgentJobs queries don't return data on some SQL 2016 Availability Group instances

The SQL Server Agent has performance objects and counters for monitoring jobs. Using the Windows Performance Monitor application I can always see the data:

perfmon

But when I try using gwmi Win32_PerfFormattedData_SQLSERVERAGENT_SQLAgentJobs in PowerShell on some servers there are no instances returned. Same results when testing with WBEMTest and wmic.exe.

When I run typeperf -qx I see a bunch of \SQLAgent:Jobs(_Total)* metrics, but how can I use a WMI query to get results from the Win32_PerfFormattedData_SQLSERVERAGENT_SQLAgentJobs or Win32_PerfRawData_SQLSERVERAGENT_SQLAgentJobs classes?

UPDATE: hmm... this may just be an issue when querying a SQL Server 2016 CU1 or SP1 CU2 Availability Group instance, although again it works on some servers but not others. Seems to stop working after applying updates, and even after restarting the system the counters don't return any instances. I have not yet been able to replicate the issue on a standalone instance, as they always return the expected data:

PS C:\> gwmi Win32_PerfRawData_SQLSERVERAGENT_SQLAgentJobs | ft -AutoSize Name,Successfuljobs,FailedJobs,Activejobs

Name      Successfuljobs FailedJobs Activejobs
----      -------------- ---------- ----------
Others                 0          0          0
Alerts                 0          0          0
Schedules             20        106          0
_Total                20        106          0

Also side note these counters are not available in the sys.dm_os_performance_counters view.

Solution

  • So after a 2nd restart of the server the WMI queries appear to be working now. I believe that the CU and SP updates can include changes to the WMI provider, which doesn't get updated until the system is restarted. The updates sometimes say that they do not require a reboot, but if WMI queries stop working after the update then restarting the server may fix the issue.