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

Apply CU2 for SQL2012 SP1 – Fix for Windows Installer starting repeatedly and causing CPU spikes !


CU2 for SQLServer 2012 SP1 is one key update which you should review and deploy if found necessary. I have found this very much needed for all my SQL2012 SP1 deployments. This CU contains a very important fix(KB2793634).

Note – The choice to deploy CU2 or just the fix (KB2793634) depends on your environment needs.

Here is why you need this fix –

As soon as you deploy SP1 for SQL2012 the Windows Installer (Msiexec.exe) process is repeatedly started to repair certain assemblies. Additionally, the following events are logged in the Application log:

EventId: 1004 Source: MsiInstaller Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’, Component ‘{0CECE655-2A0F-4593-AF4B-EFC31D622982}’ failed. The resource”does not exist.

EventId: 1001 Source: MsiInstaller Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’ failed during request for component ‘{6E985C15-8B6D-413D-B456-4F624D9C11C2}’

Source  – http://support.microsoft.com/kb/2793634

Here are the screenshots of the application log. These are shown as warnings, and could easily be missed.

InstallerIssue2

InstallerIssue1

When this occurs there is a spike in CPU.

This process is repeated every 2 minutes and can cause heavy registry usage issues.

Registry issues can cause multiple failures and a sample one is

InstallerIssue3

Note – Again, this varies from environment to environment.

Conclusion

Deploying the fix or the CU will resolve the issues related to the installer process.

Thanks for reading and keep watching this space for more.

Edit job error ” Creating an instance of the COM component with CLSID {ZZZ} from the IClassFactory failed “


Recently my friend told me that he is hitting a weird error whenever he tries to edit a job using SSMS from his machine.

I walked to his desk to get a feel of what is happening, and he was indeed right! He was hitting the below error when ever he tried to edit a step for a SQL jobs from SSMS.

I decided to grab a cup of coffee,and then slowly started looking around to see if I can isolate the problem.

The first thing I did was to check the version of SSMS which was in use.

Now things started becoming more clear. The SSMS version was 10.50.1600 which is at least 2.5 years old and we have moved far away from the early 2010’s.

Without second thoughts I decided to patch up the SSMS to latest service pack, which is SQL 2008 R2 SP1.

Meanwhile when the patch was running I just did an RDP to the server and launched SSMS to check if the issue was happening there too (Yeah, not that a great thing to do on a production server, however for some rare cases I do this)

The issue was not happening directly from the server and SSMS was running latest version.

We tried to repro the same issue after patching got completed, and viola the issue was fixed !!!

 

 

 

 

Conclusion

This scenario clearly shows the fact that there are lot many issues which get addressed whenever a new Service Pack is released and its always a good practice to patch your local SSMS too whenever a SP is made available.

Yet another reason to keep up to date on SP levels.

Thanks for reading.

“There is insufficient system memory in resource pool ‘internal’ to run this query”


During Initial versions(RTM) of SQL 2008 and R2 I have observed multiple posts related to the error “There is insufficient system memory in resource pool ‘internal’ to run this query”.

Mostly the error comes up if a Full Text Query is ran which uses compound words,however the same error comes up on different situations too and there was a BUG which was opened for this case too

http://connect.microsoft.com/SQLServer/feedback/details/484787/there-is-insufficient-system-memory-in-resource-pool-internal-to-run-this-query

The good news is that SQL Server Product team have fixed this issue and if you are facing this error in your systems(RTM) right now,then you can go ahead and deploy the latest service pack for SQL Server 2008 and R2.

SP3 for SQL Server 2008 –
http://www.microsoft.com/download/en/details.aspx?id=27594

SP1 for SQL Server 2008 R2 –
http://www.microsoft.com/download/en/details.aspx?id=26727

< Note Added on 8/2/2012 >

Incase you are having severe memory pressure,then also you can notice this error message and in that case it has nothing to do with service packs.You will have to closely verify and set MAX,MIN memory settings along with LPIM.

</Note>

SQL Server 2005 in Windows Server 2008 R2 Error “The SQL Server FullTextSearch (MSSQLSERVER) service depends the following service: NTLMSSP. This service might not be installed.”


Full text search service will be offline and effort to make it online will fail.When you check the logs you can see the error “The SQL Server FullTextSearch (MSSQLSERVER) service depends the following service: NTLMSSP. This service might not be installed.”

Resolution :  Proceed and patch SQL server 2005 to the latest service pack ( SP4).This will fix the issue.