How to change the collation of TEMPDB

Consider this scenario:
- During installation of SQL Server you defined a certain collation e.g. (A).
- You then attached a user database with a different collation e.g. (B).
- You then noticed that some queries were conflicting with the collation of TEMPDB.

It is therefore a good time to correct that mistake and change the collation of TEMPDB. There are two paths:

1. Rebuild the system databases by running the SQL Server setup again from command prompt.

   e.g. for a SQL cluster the command would look like:

"start /wait <CD or DVD Drive>\setup.exe /qb VS=<VSName> INSTANCENAME=<InstanceName> REINSTALL=SQL_Engine SKUUPGRADE=1 REBUILDDATABASE=1 ADMINPASSWORD=<StrongPassword> SAPWD=<NewStrongPassword> SQLCOLLATION=<NewSystemCollation> SQLACCOUNT=<domain\user> SQLPASSWORD=<DomainUserPassword> AGTACCOUNT=<domain\user> AGTPASSWORD=<DomainUserPassword>"

2. Find a SQL Server 2005 that is in the same build with the one you are troubleshooting. Take the model database from that SQL server and restore it on your SQL Server. After restarting the SQL Server service, the database engine will use the new model database to rebuild TEMPDB. So you will have now a TEMPDB with a new collation.

  Note1: I have only tested this in a lab and I am not sure this is even a supported action, but I know it works.
  Note2: The master database will retain its old collation.