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?
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.