I was trying to setup database mirroring alerts on one of my production database servers and I looked into the article on the MS site, see http://technet.microsoft.com/en-us/library/cc966392.aspx on mirroring alerting best practices. I then proceeded in trying to setup same, I ran the script below.
USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N'Mirroring_Alert', @enabled=1, @delay_between_responses=1800, @include_event_description_in=0, @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', @wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 5 ', @job_id=N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name=N'Mirroring_Alert', @operator_name=N'DBA_GROUP', @notification_method = 1 GO
I then got the error message below.
Msg 22022, Level 16, State 1, Line 0 SQLServerAgent Error: WMI error: 0x80041003. Msg 14511, Level 16, State 1, Procedure sp_verify_alert, Line 300 The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax.
I ran thesame code on my desktop and it was fine, but I was thinking what could be the problem here.
I checked everything to make sure that the syntax was right and there wasnt anything wrong, bumped across a few KB’s about thesame problem, but it was primarily being caused by server names being longer than 15 characters and this applied to SQL 2005, my SQL instance was SQL 2008 and I then thought what could be going wrong. After spending the hour battling this, and I have a rule that any problem taking longer than an hour to fix, I need to get my head cooled out, pop out for a 10 minute break and fresh air and then give it another go. It seem to work for me time and time again, I came back, thought !! Hang on a minute, lets check the SQL error log to see if there are any pointers to the root cause, on there I saw errors as follows.
Date 6/13/2011 11:45:33 AM Log SQL Server (Current - 6/13/2011 10:01:00 AM) Source Logon Message Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: The account is disabled. [CLIENT: <local machine>]
Ahh, this could be it. When I setup SQL servers I often disable the builtin/Administrators login as this is a security risk. It looks like the WMI service is using the local system account and this is obviously disabled and thus causing the problem highlighted.
I changed the WMI service account such that its a domain account and permissioned it on SQL server and pesto !! The problem is solved.