Curious case of SYS.MESSAGES


A strange error struck me last week, and this post is all about that.

What you normally expect when you run SELECT * FROM sys.messages? The query will return a row for each message_id or language_id of the error messages in the system, for both system-defined and user-defined messages.

Rather than returning the rows, system generated an error for me:

Msg 18058, Level 17, State 0, Line 1
Failed to load format string for error 362, language id 1033. Operating system error: 317(The system cannot find message text for message number 0x%1 in the message file for %2.). Check that the resource file matches SQL Server executable, and resource file in localized directory matches the file under English directory. Also check memory usage.

As always, my first instinct was to check what version of SQL Server I was running. It was SQL 2008 R2 SP3.

Build : Microsoft SQL Server 2008 R2 (SP3) – 10.50.6000.34 (X64)

I knew that there are already some issues which we hit during our SP3 patching, and this specific portion of the error message which said “Check that the resource file matches SQL Server executable, and resource file in localized directory matches the file under English directory” added to my suspicion that SP3 upgrade process might have messed-up some files.

A quick search on the MSDN forum revealed info about file sqlevn70.rll which is available in the installation folder( MSSQL\Binn\Resources\1033\sqlevn70.rll) which might not got updated when SP3 update was done, and this info opened new doors.

Some of the folks had success by replacing this file from a server(same build). I tried the same thing by renaming the old file and copied the new one.

The below picture will highlight the size/date difference for both files :

Capture

 

After this file changed I tried to run the same query SELECT * FROM sys.messages, and got the same error. I just tried this to see if changes can reflect on the fly or I need to restart the services. A restart was indeed needed and the issue was resolved after the same.

Conclusion:

The same error might be happening for your servers too, and I’m still working with Microsoft to see if there are any additional implications (critical ones) because the installer is not updating the files for some specific cases. I will be updating this post in case I have more info in this regard.

Thanks for reading and keep watching this space for more.

Advertisements

3 thoughts on “Curious case of SYS.MESSAGES

  1. Pingback: Sys.Messages Error – Curated SQL

  2. I am running Sql Server 12.0.4422 in RDS. With this newer version, I am not able to reproduce the problem described above. I think this may be an issue with 2008 but later versions are working.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s