SQL Server Database Mail – It’s vaster than you think

SQL Server Database mail, since its inception in SQL Server 2005, has been a very important part of DBA’s daily life. This is the main part of your automatic alert generation system and this saves you a lot of work in day to day life. You don’t have to go to the servers one by one and check whether there is something wrong. You get all such information daily in your inbox when you jump in the office, courtesy Database Mail. You rely heavily on it.

Very rarely we see that there is an issue with database mail. Even if there is an issue, it is generally a common one and you easily get a fix by doing obvious things like checking whether database mail is enabled in SQL Server Agent or not, checking whether database mail extended procedures option is turned on or not, checking whether service broker is enabled in msdb or not and some other common things.

We love right clicking on database mail to send test e-mail and feel great when we see that email in our inbox. Isn’t it? But I didn’t know that one fine day I’ll get frustrated by right clicking over and over again to send test e-mail and waiting for email to hit my inbox. I checked all the basic stuff, my finger got tired of right clicking to send test e-mail BUT the Database mail was still angry with my inbox.

So what’s next? Well, you’ve got to fix what has broken, so let’s see how it all unfolded.

We had two instances of SQL server 2008 (which will soon be called legacy but we still have these in most environments) running on windows cluster and database mail was not working on both of those. The errors I was facing on both the instances were kind of similar (like below):

• The mail system failed to initialize; check configuration settings.
• Unable to start mail session.
• Mail configuration information could not be read from the database.

I wanted to get rid of these errors as soon as I can so below is how I was able to fix these:

Instance1

When we install SQL Server, there are two files that need to be there (along with others) in the installation directory. The file names are msxmlsql.rll and msxmlsql.dll. These are required for many functionalities. The location of these files (depending on the version) is:

C:\Program Files\Microsoft SQL Server\100\Shared\Resources\1033\msxmlsql.rll

C:\Program Files\Microsoft SQL Server\100\Shared\msxmlsql.dll

When I checked in msdb.dbo.sysmail_event_log, I came across a message saying “Failed to load Msxmlsql.dll”

I went to the folders mentioned above and I saw that the files were missing. This made me optimistic and from inside I jumped a little in joy thinking I’ve got the fix. But I controlled myself and thought where do I get the files from?

Did you guess it?

Yes, from our SQL Server 2008 Setup DVD. I searched for those files in setup, copied the files, placed these at the location mentioned above, restarted SQL Server Agent and that’s it. Database mail and my inbox hugged each other. The email messages started to flow and I was the most happy with this patch-up.

Great!! One out of the way and I grabbed the second one.

Instance2

This was also an interesting fix and I did not know till I searched for resolution.

There are many stored procedures related to database mail in msdb, we all know that. One of those stored procedures is sysmail_help_admin_account_sp which gets called by database mail binaries. If you check the definition of this procedure in new versions, this procedure returns many columns and one of those columns is timeout column.

The catch here was that the older versions of this procedure did not return the timeout column.

So, what created the mess?

Well, sometime ago, we had service pack upgrade on all SQL server instances in our environment and for this instance we installed SP4.

While looking for resolution, I checked the modified date of stored procedure sysmail_help_admin_account_sp in msdb.sys.objects, it was the date way before the service pack upgrade happened.

So what does this mean? This means that the procedure sysmail_help_admin_account_sp (along with some other procedures) did not upgrade with service pack upgrade so msdb had older version of this procedure.

However, the other SQL Server binaries (along with database mail binaries) upgraded fine and upgraded binaries were expecting sysmail_help_admin_account_sp procedure to return timeout column but as this procedure was at the older version it could not return timeout and hence the database mail was throwing the error.

It was clear that there was some kind of an issue with service pack upgrade and it was ignored by the one who did it.

I decided to install service pack again and it completed successfully this time. And guess what, the database mail for this server too smiled back at me. I checked the upgrade date of this procedure and it was current date now. All the stuck emails flooded in my inbox and I breathed a sigh of relief

Phew….These two issues brought brilliant learning to me related to database mail. Although we hate issues but when you get to resolve these you feel good that you got to learn something. Really, Database mail is actually vaster than we all think. Hope it helps you some day.

And hey, I forgot to tell you when it all happened. It happened on the night before Valentine’s Day and believe me, it was easier to make database mail smile. You know what I mean. Right?

Cheers!!

Regards,
Yogeshwar Phull

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter | Follow me on FaceBook
————————————