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

One error which you don’t want to see while doing upgrades – Missing MSI !


The cached MSI file ‘C:\Windows\Installer\<name>.msi’ is missing !!! This is one error which you don’t want to see when you are trying to do a service pack upgrade or a CU update during a critical change window.  Here is what the error looks like  –

Missing MSI1

If you are hit with this error then there is definitely good amount of help out there, but the amount of time it takes to fix the error can vary from case to case. There is a good amount of chance that there are multiple MSIs which are missing, and you will need to take action to replace the missing MSIs before trying to re-run the SP/CU setup again.

So why this error comes up ? The short answer to this question is that the Windows Installer Cache(Under C:\Windows\Installer) on your server is compromised. This folder is used to cache important setup files, and these files are needed during situations like patch upgrades or even for uninstalls.  If this cache is compromised, then you will definitely need to work with your  windows admins to understand why it was compromised.

As mentioned earlier there is a good amount of chance that multiple MSIs are missing, and replacing them is time consuming.

Microsoft has a great KB article http://support.microsoft.com/kb/969052 which will explain the entire process of taking corrective actions to fix this problem, and there is a smart VB script which will tell you the details of files which are missing and it will provide you commands to take corrective action. I would definitely encourage you to read this article once.

In short here are the steps which I followed when faced this error while doing an SP2 upgrade for SQL2008 R2  –

1. Copied the FindSQLInstalls.vbs script from the KB and placed under C:\Script

2. Open a command window(As Admin) and ran the command

Cscript FindSQLInstalls.vbs %computername%_sql_install_details.txt

This created a txt file,and that file is your best bet for fixing the missing MSI errors.

3. Opened the txt file to see the amount of details it has on the missing msi files.  I was interested in the Action needed area where it will provide a copy command with the msi details.Something like –

Copy “O:\SQLINSTALL\SQL2008StdRTM\x64\setup\sql_bids.msi” C:\windows\Installer\df2ee59.msi

I ran the command(As Admin) and it copied the files to the cached folder. I scrolled down the txt file to understand that there are multiple MSI files which are missing,and I had to run the copy command multiple times(I counted till 15 !).

4. Once I completed running all the copy commands which were available in the txt file, I ran SP setup again and it was successful this time.

Being proactive

If you have a real tight change window and you don’t want to deal with these errors, then I would recommend running the VB script in advance for your server and see if its reporting any missing MSI for your SQL Server. If there are errors, then you can fix this in advance and ensure that the upgrade will go smooth. If there are no errors after running the VBscript, then there is a level of confidence that you will not hit the missing msi errors for sure.

Thanks for reading and keep watching this space for more !