SQL Server 2012 Collation Issue – After Update KB3072779

collationsql-server-2012windows-server

If found the following error while starting my SQL Server 2012 instance, after the update KB3072779 performed by Windows Update (Windows Server 2012 Foundation).

Creating procedure sp_sqlagent_get_perf_counters...
2016-11-14 12:09:07.84 spid5s      Error: 468, Severity: 16, State: 9.
2016-11-14 12:09:07.84 spid5s      Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "Latin1_General_CI_AS" in the equal to operation.

So now I'm unable to start the SQL Server instance. I managed to run it with -t902 and perform some queries.

I did check the following:

select serverproperty('collation')

which returned:

Latin1_General_CS_AS

also

select name, collation_name from sys.databases

which returned:

master  Latin1_General_CI_AS
tempdb  Latin1_General_CI_AS
model   Latin1_General_CI_AS
msdb    Latin1_General_CI_AS

From what I've read so far, system databases must have the same collation as the server. What I'm not aware of is what happened here and what should I do now.

Did this update change the server collation?

What can I do now to run my instance normally?

===========================

EDIT: I finally arrive to solve the problem by following the steps described in one of the links posted by @hot2use

https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation

Best Answer

I would go and look at the setup.log files for the Service Pack 3 Upgrade. They should be found here:

Each execution of Setup creates log files are created with a new timestamped log folder at %programfiles%\MicrosoftSQL Server\110\Setup Bootstrap\Log\. The time-stamped log folder name format is YYYYMMDD_hhmmss. When Setup is run in an unattended mode, the logs are created at % temp%\sqlsetup*.log. All files in the logs folder are archived into the Log*.cab file in their respective log folder.

More information can be found here: View and Read SQL Server Setup Log Files (Microsoft)

This might provide you with information on what happened.

If you feel like rebuilding you SQL Server instance with the correct collation, then you could try the solution here:

How to change server level collation for a SQL Server Instance

There are slight variations of above post involving re-installing the system databases and or just modifying some objects:

It's not highly recommended and is best tested in a non-productive environment first. Currently there are no documented cases of the collation being changed during the setup of a service pack on SQL Server. If you don't find any information in the setup logs, then I would try and contact Microsoft and open a case/ticket.

Related Question