Bad Data object SQLServer:Access Methods does not exist

MariusE

Hi!

We are running a Windows 2012R2 cluster with SQL 2014, when I add my instances (we have several instances), the following is always returned: object SQLServer:Access Methods does not exist

I get the same on our SQL 2012 instances.

All our instances are named instances, and I'm able to add Access Method and Buffer Manager to a perfmon session on the server.
However, they are named MSSQL$InstanceName:Access Method

Doesn't SQL Monitor support named instances, any workaround for this?
I would also like it if the instance name were used instead of (local).

Thanks!

Regards
Marius E

Brian Donahue

Most of the time this is because of performance counter corruption or disabled performance counters. I have sent you some more specific instructions via email.

JoeGT

I too am having this same issue with SQL 2008 R2 named instances (ie MSSQL$<instance_name> ) and the exact same error message.


I can also confirm that I am able to successfully add the Access Method and Buffer Manager counters to a perfmon session running locally on the server

Can you please forward on the same instructions by email to myself ?

The mere fact that myself and the original poster are able to add the counter locally on the server suggests to me it is not likely that they are corrupted nor disabled and more that the issue is with SQL Monitor attempting to connect to perfmon using the named instance name

Another check I did was to look for the existence of the counters (and values for them) from SQL :

use master

select * from sysperfinfo where object_name like '%Access Methods%'
select * from sysperfinfo where object_name like '%Buffer Manager%'

This showed that for all of these named instances the counters appeared to be running just fine.

Brian Donahue

Thanks for your interest. Usually it's a case of rebuilding the performance counters, as described here:

http://dba.stackexchange.com/questions/ ... e-counters

More rarely, someone has disabled them, and presumably forgotten they'd done it. Then you can re-enable them using Microsoft's exctrlst tool.

Rarer still are some edge cases like the one at the start of this topic, that seem to be something to do with cluster security (not sure yet).

Your query is looking at MSSQL Dynamic Management views and although that would seem like the most likely way SQL Monitor should query perf data, it is actually using WMI and some Remote Registry queries on the Windows installation.