Why is sp_update_alert failing?

Question

In SQL Server 2012, I have a Job Agent Alert set up as follows:

EXEC msdb.dbo.sp_add_alert @name=N'Deadlock Alert', 
        @message_id=0, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @performance_condition=N'Locks|Number of Deadlocks/sec|_Total|>|0', 
        @job_id=N'00000000-0000-0000-0000-000000000000'

I have a job scheduled to run every weekday at 5:30pm that executes this step:

EXEC msdb.dbo.sp_update_alert @name = N`enter code here`'Deadlock Alert', @enabled = 0;

I have another that re-enables the alert at 8:00am every weekday. Both jobs (and the alert) have been working fine for months in dozens of instances. But the job at 5:30pm on Friday failed on one instance with the following messages:

The specified object_name ('Locks') does not exist. [SQLSTATE 42000] (Error 14262) The specified object_name ('Databases') does not exist. [SQLSTATE 42000] (Error 14262). The step failed.

If I execute the sp_update_alert command directly for that alert (with enabled set to either 0 or 1). I get this error:

Msg 14262, Level 16, State 1, Procedure sp_verify_performance_condition, Line 50 The specified object_name ('Locks') does not exist.

I tried disabling the Alert using the SSMS UI and get:

The specified object_name ('Locks') does not exist. (Microsoft SQL Server, Error: 14262)

This pair of jobs is still working without error in all of the other instances. What could be causing sp_update_alert to fail in this was on just one instance and only since Friday?

[Added:]

The enable and disable jobs also enable and disable another alert, which looks at the SQLServer:Databases|Data File(s) Size (KB)|tempdb counter and those commmmands throws the error "The specified object_name ('Databases') does not exist". Again, only in this one instance. The same jobs and alerts still work fine on another instance on the same server.

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

Verify that the SQL Server Agent account has the necessary permissions to query the performance counters DMV (sounds like your agent account has recently been changed). According to the documentation:

Requires VIEW SERVER STATE permission on the server.

So:

GRANT VIEW SERVER STATE TO [SQL Agent account];

Also make sure that as that login (or any login with VIEW SERVER STATE, such as sa), you can see results come back from the DMV. I have heard of many cases where the performance counters simply come up empty in SQL Server, and this dates back to SQL Server 2000 with sysperfinfo. Several sources suggest the following as a solution from the command line (followed by a reboot):

lodctr "<path to binn>\binn\sqlctr.ini"

Of course Microsoft closes most of these reports as no repro (which doesn't surprise me, as I haven't seen this symptom directly, and would have no idea how to reproduce the issue).

Missing sys.dm_os_performance_counters
sys.dm_os_performance_counters only showing XTP-related data (fix was applying a CU)

Also some advice in these threads, which you are welcome to try (these are quite long-standing and I'm not going to try and summarize and repeat the info here):

Why is sp_update_alert failing?

1 Answers1