Change Collation setting for TemDB

Hi All,

Today i received a problem from one of the DBA, His problem was unique, he wanted to change the collation setting of tempdb to Japanese_BIN.

At first this seems to be a normal issue, but soon i realized this is something which is not known to many, So i decided to document the solution here, this may prove to be a life saver for someone:

Problem: “I want to change the tempdb collation in MSSQL sever.
i am getting the below error..
alter database [tempdb] collate Japanese_BIN
Server: Msg 3708, Level 16, State 5, Line 1
Cannot alter the database ‘tempdb’ because it is a system database.”

Solution: Changing the tempdb Collation
The collation of tempdb cannot be changed by using the ALTER DATABASE statement—SQL Server does not allow this since tempdb is part of the system database.

Note: that tempdb uses the collation of the model database. Since there is a way to change the collation of the model database, we inferred that we should be able to change the collation of tempdb. Recall that the model database can be backed up and restored.

So, for example, if we have another instance of SQL Server running with a default collation of Japanese_BIN, we can back up the model database from the “Japanese_BIN” server, restore it on the target server, and then restart the MSSQL service on the target server.

Another solution can be reinstalling SQL Engine using collation setting you need.

Regards

Sarabpreet Anand

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook