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
www.PracticalSqlDba.com
