DBMail has Suddenly Stopped Working

The other day we ran into a strange Database Mail issue here in SQL support. Customers were running into issues where DBMail would suddenly stop working after doing service pack upgrades to their SQL Servers.This was happening on both SQL 2005 and SQL 2008.

The error was also unusual in that the word "timeout" could make you believe we were dealing with a performance issue. Here is what you will see in the SQLAgent.OUT log file.

2009-11-03 22:57:37 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2009-11-03 22:57:38 - ! [260] Unable to start mail session (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database. ---> System.IndexOutOfRangeException: timeout
   at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
   at System.Data.SqlClient.SqlDataReader.get_Item(String name)
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
   --- E)

Turns out that the "timeout" is a new field name in a msdb table and hence the DBMail binaries are looking for this field when it calls our stored procedure!

Additional Errors you may encounter trying to run DBMail commands manually:

2009-11-03 22:57:38 - ! [355] The mail system failed to initialize; check configuration settings

Exception Information

Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException

Message: Mail configuration information could not be read from the database.

Data: System.Collections.ListDictionaryInternal

TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)

HelpLink: NULL

Source: DatabaseMailEngine

StackTrace Information

   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)

   at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)

   at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)

   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)

Exception Information

Exception Type: System.IndexOutOfRangeException

Message: timeout

Data: System.Collections.ListDictionaryInternal

TargetSite: Int32 GetOrdinal(System.String)

HelpLink: NULL

Source: System.Data

StackTrace Information

   at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)

   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)

   at System.Data.SqlClient.SqlDataReader.get_Item(String name)

   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID

In every case "GetOrdinal" is part of the error messsage. After much research, here are the scenarios in which you will encounter this problem:

SQL 2008 Scenario:

Your current SQL 2008 instance is SQL 2008 RTM CU 6 or higher.
You then upgrade that instance to SQL Server 2008 Service Pack 1 and no higher than Cumulative update 1 (CU1).

SQL 2005 Scenario:

Your current SQL 2005 instance is SQL 2005 Service Pack 2 CU13 or higher.
You then upgrade that instance to SQL Server 2005 Service Pack 3 and no higher than Cumulative update 3 (CU3).

Starting with Cumulative Update Package 6 for SQL Server 2008 and Cumulative Update Package 13 for SQL Server 2005 Service pack 2, Database mail recieved new versions of the binary files and schema definitions. When applying service packs to these instances as outlined in the Symptoms section, the upgraded binaries are not touched but the stored procedures associated with Database Mail get overwritten by instmsdb.sql script in SQL Server 2008 or by sqldbupg.sql in SQL Server 2005. Theses scripts contain older stored procedure definitions that don't include the "timeout" column. When the DBMail binaries make a call to the system stored procedure sysmail_help_admin_account_sp, they expect a "timeout" column to be returned by the stored procedure. Though this column exists in the underlying schema\table, the newer version of the stored procedure does not return this column and hence you encounter IndexOutOfRangeException, on the GetOrdinal call.

To fix DBMail, all you need to do is upgrade the just installed Service Pack to the latest cumulative update for that SQL version. This will correct the schema definitions for the stored procs (DBMail dlls are the same and the underlying table remain the same).

Note: Use the resolution appropriate for your environment:

SQL Server 2008: Apply Cumulative Update Package 2 (or a higher version) for SQL Server 2008 Service Pack 1.

SQL 2008 SP1 CU2:970315 Cumulative update package 2 for SQL Server 2008 Service Pack 1
http://support.microsoft.com/default.aspx?scid=kb;EN-US;970315

SQL Server 2005: Apply Cumulative Update Package 4 (or a higher version) for SQL Server 2005 Service Pack 3.

SQL 2005 SP3 CU4: 970279 Cumulative update package 4 for SQL Server 2005 Service Pack 3
http://support.microsoft.com/default.aspx?scid=kb;EN-US;970279

Look for a KB article in the next 2 or 3 weeks that will completely document this situation. I wanted to give you a "heads up" on this so that you can resolve it quickly and without too much frustration.

Eric Burgess
SQL Server Escalation Team