Collation conflict

Recently one of my clients got an issue with collation conflict when he wanted to access properties of his database via SSMS. Issue looks like this:

Cause:

Error means that tempdb as a different collation (more restrictive) than collation of user database. So server cannot load properties to the temp db because data can be truncated. Remember that server will always try to protect you when some of your actions will lead to data truncation.

Solution:

Microsoft recommend to "repair installation"

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName 
/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] 
/SQLCOLLATION=CollationName

More here:

But be alerted! It means your system databases will be deleted and created from the scratch, so you will lose all data from those db's.

I think there is more simple and harmless way. First we need to understand the process of tempdb collation setting. Tempdb is rebuilding every instance restart from model db. So if we change collation of model db, server will create tempdb with new collation after instance reboot. But there is one caveat. Collation of model db cannot be changed, because it is a system database. This collation has been set when you install SQL instance.

I see two options here.

1. We could install new SQL server instance somewhere on test and in installation setup pick correct collation. In my case it was Czech_CI_AS. Then shutdown affected instance and newly installed instance. Rename model.mdf and model.ldf files on old instance, then copy model db files from newly installed instance. Start the affected instance and check if the issue persists. If looks ok, you can uninstall new instance and remove renamed old model files after few days.

Remember that you need to have exact same version of model db on both instances.

Don’t forget to check whether server versions are same before moving model using:

 We could try to bypass process of installation using this approach.

1. Stop SQL Service

2. Make a copy of model database for data (model.mdf --> model_old.mdf) and log file (modellog.ldf --> model_old_log.ldf)

3. Start SQL Server and then Attach modelold

4. Run  SQL command as below to change the collation

6.Move model.mdf and modellog.ldf to backup folder.

7.Rename model_old.mdf --> model.mdf and model_old_log.ldf --> modellog.ldf

8.Start sql server and run following to ensure tempdb is working in new collation:

NOTE: Full script: CollationConflict.sql

So in this way we just change model db and not affect other system databases by "repairing" them. If you using different strategy, share it bellow in comments.