Error in WMI Event Alert for DB Mirroring

Question

Hi,

I am trying to create an WMI Event alert on MS SQL Server 2008 R2 (RTM) std edition. I have default MSSQLSERVER instance available on the DB Server.

I want to create the alert to notify when automatic failover happens, i am getting the following error :

SQLServerAgent Error: WMI error: 0x80041003.
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. (Microsoft SQL Server, Error: 22022)

when i am trying through T-SQL

EXEC msdb.dbo.sp_add_alert
    @name=N'DB Mirroring: State Changes',
    @category_name=N'Database Mirroring',
    @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
    @wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 8',
    @enabled=1

Getting the error :

Msg 14262, Level 16, State 1, Procedure sp_verify_alert, Line 245
The specified @category_name ('Database Mirroring') does not exist.

Kindly suggest possible workaround/resolution for the same.

Thanks & regards,

Animesh

Answer 1

Similar thread http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/64a314c7-ce54-4bf3-86ad-ec7c8b140638/

---

http://uk.linkedin.com/in/ramjaddu

Answer 2

Hi RamJaddu,

Its similar but not the same, any further suggestion on the same will be highly appreciated.

Thanks in advance.

Regards,

Animesh

Answer 3

Try running the following before creating your alert

    IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N'Database Mirroring' AND category_class = 2 )
        BEGIN
            EXEC msdb.dbo.sp_add_category @class = N'ALERT', @type = N'NONE', @name = N'Database Mirroring' ;
        END ;

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 4

Hi,

I have execute the suggested script and got executed successfully. Now when i am trying to create the alert i am getting the error

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.

Please suggest if any further resolution for this.

Thanks in advance