SQL server 2005 Database Mail stopped functioning after installing Service Pack 3

Recently I have upgraded one of our SQL server 2005 instance to Service Pack 3 and later point of time we have noticed that the database mail in that instance stopped working with the following error in Database Mail error log.

[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

Unfortunately in our QA environment , database mail is not used extensively and did not noticed this issue. After updating the service pack in the live , all our monitoring and alert mail stopped working.

The KB article 2008286 is clearly stated that the solution for this problem is to update the Cumulative Update 4 (CU4)  but for that  I need to go through the change management process and it is time consuming.

After spending some time, I have found a work around for this problem by updating sysmail_help_admin_account_sp stored procedure in msdb database. 

The reason behind this issues is , when the modified database mail binaries ( modified as part of  service pack 3)  makes call to the above mentioned stored  procedure , it expects a timeout column in the result set of the stored procedure,but the current version of stored procedure is not returning the timeout column and hence encounter IndexOutOfRangeException.The modified version of the stored procedure is available here to download. This stored procedure will be replaced by the Microsoft version while updating the CU4 for Service Pack 3 or later version 

If you liked this post, do like my page on FaceBook at http://www.FaceBook.com/PracticalSqlDba