4

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.

  • Could this be because any database taken offline or removed. Because I can also see specified object_name ('Databases') does not exist – Shanky Feb 15 '16 at 14:48
  • Sounds to me like your sys.dm_os_performance_counters view is volatile. Have you had any issues with missing performance counters on this machine? Have you tried changing your performance condition to SQL Server:Locks|Number...? – Aaron Bertrand Feb 15 '16 at 15:14
  • @Shanky: No databases are offline, and none have been removed in months. – Mark Freeman Feb 15 '16 at 15:39
  • @AaronBertrand: I haven't noticed any issues with the counters, but I don't look at them much (I rely on a monitoring tool, and it hasn't indicated any issues either). This server has multiple instances. I didn't see a SQL Server:Locks but I did see MSSQL$RELEASE:Locks. I dropped the alert and tried re-adding it using each of those but in both cases I get "The specified object_name ('Locks') does not exist.". Nothing has changes with regard to the number of instances on this server in about a year. – Mark Freeman Feb 15 '16 at 15:42
  • If you're using a monitoring tool, why don't you use that to capture your deadlocks (along with all the relevant information you need to actually investigate and resolve them), rather than just getting an alert every time a deadlock happens? – Aaron Bertrand Feb 15 '16 at 15:44
1

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

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):

  • SA now has VIEW SERVER STATE, but I still can't recreate the alert without getting "The specified object_name ('Locks') does not exist." – Mark Freeman Feb 15 '16 at 17:51
  • @MarkFreeman SA? Do you mean the SQL Agent account? Which account is being used to run agent? Have you logged into the machine as that account to verify that permissions were granted correctly? – Aaron Bertrand Feb 15 '16 at 17:53
  • Sorry to muddy the waters. I meant to type "The Agent service account" not "sa". That account doesn't have RDP rights so I can't log in under that account. But in the SSMS UI, it does show as having View server stated checked in the Grant column under Securables. – Mark Freeman Feb 15 '16 at 18:26
  • You can log in remotely using a new instance of SSMS invoked with runas to actually test that the account can connect, select from the view, etc. No RDP required – Aaron Bertrand Feb 15 '16 at 18:28
  • "select * from sys.dm_os_performance_counters" gets me nothing in that instance, whether I use my usual domain account (which is sysadmin) or the Agent service account. If I connect to the other instance on the same server (using either my domain account or the same Agent service account), I get 1262 rows. Something is obviously set up differently between the instances that impacts more than just the service account, but I don't see what it is. – Mark Freeman Feb 15 '16 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.