Category Archives: Service Packs & CUs

Curious case of SYS.MESSAGES

Posted on by

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.

SQLServer 2008 R2 SP3 released, Now its testing and deployment time!

Posted on by

As promised Microsoft released SQL Server 2008 R2 SP3 last Friday, and now this is your opportunity to test and patch all the existing SQLServer 2008 R2 to this latest build.

SP3 will be the last and final service pack for SQL Server 2008 R2 ,and it will be a good idea to patch your servers just because the mainstream support for 2008 R2 is already finished. Yes, you heard that right mainstream support for SQLServer 2008 R2 ended on 7/8/2014.

You might want to get onto extended support if you don’t have solid plans to upgrade to SQLServer 2012/SQLServer 2014 (Or probably just skip SQL Server 2012 and go to 2014).

So what’s mainstream support and extended support?

Mainstream Support –

Mainstream support means Microsoft supports a product with its full offerings including paid incident support, hot-fix support, security updates, etc.

Extended Support –

Microsoft still provides security updates at no charge to all customers.

You can still call CSS or create a case online per the normal support offerings (Pay per incident, Premier, etc.)

You cannot obtain a non-security hot-fix from Microsoft free of charge. In order to obtain a non-security hot-fix, we must purchase an Extended Hot fix Support Agreement.

Extended Hot fix Support Agreements are available for Premier customers.

What’s covered in SP3 – Contains fixes from all Cumulative Updates that were released since SP2 for SQL Server 2008 R2, i.e. till SP2 CU13.

It also contains 2 additional fixes and you can read them here.

If you are on an extended support agreement and you open a case with Microsoft, then probably the first thing they are going to ask is if you are running SP3 or not?  So test this build real hard and plan to deploy it across the enterprise

OR

Upgrade to SQLServer 2014 for lot of goodness!

Thanks for reading and keep watching this space for more!

CU3 for SQL2012 SP1 – Couple of interesting hotfixes.

Posted on by

Here comes CU 3 (Build 11.0.3349.0.) for SQLServer 2012 SP1. You can download the same from http://support.microsoft.com/kb/2812412

Similar to CU2 there are some interesting hotfixes which are included in this CU release, and its worth testing it out.

The ones which attracted my attention are –

2800013

(http://support.microsoft.com/kb/2800013/  )

 FIX:   Access violation when you put a FILESTREAM-enabled database offline in SQL   Server 2008 R2 or in SQL Server 2012

 

2803529

(http://support.microsoft.com/kb/2803529/  )

 FIX:   Poor performance in SQL Server 2012 when you run a SQL Server trace

 

2807974

(http://support.microsoft.com/kb/2807974/  )

 FIX:   Error 3456 after you add new file groups to the principal database in a SQL   Server 2012 asynchronous database mirroring environment

 

2813214

(http://support.microsoft.com/kb/2813214/ )

 FIX:   CPU spike when there is no load on a server after you install SQL Server 2012   on the server

 

2819662

(http://support.microsoft.com/kb/2819662/  )

 SQL   Server 2012 experiences performance issues in NUMA environments

The last 2 fixes attracted me a lot, however these are type of issues might not occur for all the environments. The CPU spike fix has a situation like a server with 64 or 128 cores +NUMA and 256GB – 1TB of Memory.These specifications are beyond imagination for small environments and majority of the enterprise(As of now !),so these might not even be a concern for the admin’s.

Conclusion

The decision to test or deploy CU3 for SQL2012SP1 varies from environment – environment and your approach towards CUs.

Thanks for reading and keep watching this space for more.

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

Posted on by

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.

Cumulative Update 1 for SQL Server 2012 – Why am I interested in this ?

Posted on by

During the month of April 2012 Microsoft released CU1 for SQL Server 2012.

Earlier I was focusing only on Service Packs (SP) whenever they are released. I moved on from this earlier strategy of ignoring CU’s.

I have decided to focus on CU’s too whenever they are released, and made up my mind to read/understand about the hot fix’s which are included in the CU.

Some of the fixes which are included in CU1 for SQL 2012 which grabbed my attention is mentioned below. You will need to refer to http://support.microsoft.com/kb/2679368 for the full list of fixes.

1. FIX: Low CPU spikes at a set interval even without user activity in SQL Server 2008 R2 or in SQL Server 2012.

This issue has got a very interesting symptom –

You have a huge database which has heaps and you have enabled Read Committed Snapshot Isolation (RCSI).You might experience low CPU spikes every 5 minutes without any user activity!

2. FIX: Backup fails in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012 if you enable change tracking on the database

The symptoms for this issue are the backup will fail if the database is enabled for change tracking and the database has been recovered at least once after CDC was enabled.

I tried to repro this situation under RTM build, but never encountered this fix. In case you are getting this error for the above mentioned situation, then you can test out CU1.

3. FIX: It takes a long time to restore a database in SQL Server 2008 R2 or in SQL Server 2008 or in SQL 2012

The symptom of this issue is long duration for restoring databases which has many Virtual Log Files (VLF’s).

4.FIX: SQL Server Configuration Manager takes a long time to open if many SQL Server 2008 R2 or SQL Server 2012 instances are installed.

The symptom is Config Manager taking long time to open in case there are many instances configured on the same server. I don’t prefer running multiple instances for a real busy OTLP environment as sharing resources between the instances and management is a tough deal. These points are discussed well during initial phase of the planning itself.

Conclusion

The above mentioned lists of fixes are just the ones which grabbed my attention, as these issues might be common.

In case you are facing any issue and that issue has been fixed as per the KB article mentioned above, then you should test/validate CU1 and plan for deployment.The strategy of testing/deploying CU’s varies from environment to environment and this should be thoroughly discussed among the DBA team and well documented.