Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for June, 2011

SQL Agent alerts not being sent ! Its weird ! Here is the fix for [264] An attempt was made to send an email when no email session has been established

Posted by rahmanagoro on June 13, 2011


I recently setup some SQL agent alerts but I decided to test them out and make sure they actually work, guess what it wasnt actually working and I wasn’t getting any alerts.

In view of this, I then recreated the datbase mail settings on the database server, I have come across this problem before and the fix then was to restart the SQL agent service and the problem was fixed, but this one was slighly different, I have restarted a few times to avail.

One thing that I did see in the SQL agent error log is as follows.

[264] An attempt was made to send an email when no email session has been established

After checking online and not finding a solution that does fix my problem, I decided to do the following.

Open up management studio >> SQL server agent >> Right click properties >> Alert System

Then untick the Enable mail profile.

Restart SQL agent and click OK.

 

Repeat the steps

Open up management studio >> SQL server agent >> Right click properties >> Alert System

Then tick the Enable mail profile.

Restart SQL agent and click OK.

Advertisements

Posted in SQL 2008, SQL Administration, Tips | 3 Comments »

Setting up WMI query and getting error SQLServerAgent Error: WMI error: 0x80041003.

Posted by rahmanagoro on June 13, 2011


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.

Posted in Database Mirroring, Management, SQL 2008, SQL High Availability, Tips, WMI | 1 Comment »

Be Careful ! SSAS Analysis services cannot be uninstalled from a SQL cluster once installed

Posted by rahmanagoro on June 6, 2011


Read the rest of this entry »

Posted in Clustering, SQL 2008, Tips | 1 Comment »