Systems Engineering and RDBMS

Changing the collation of the tempdb database

Posted by decipherinfosys on September 2, 2008

We cannot use the “ALTER DATABASE” command to change the collation of the tempdb database. But there is a way to make this change – tempdb get’s it’s collation from the setting for the model database on the instance. So, if we can somehow change the collation of the model database and re-start the service, tempdb should inherit that collation. Let’s try that out:

select databasepropertyex (‘model’, ‘collation’)
go

—————————–

SQL_Latin1_General_CP1_CI_AS

select databasepropertyex (‘tempdb’, ‘collation’)
go

—————————–

SQL_Latin1_General_CP1_CI_AS

Now, let’s change the collation of the model database. Since model is a system database, one cannot change the collation of this database by using the ALTER database command – however, the model database can be backed up and restored. So, if we have another instance where the default collation is say “Chinese_PRC_CI_AI”, then we can backup and restore from that instance to our instance and re-start the service in order to get the collation that we want for tempdb on our instance as well.

This would rarely be a need in real life scenario since if you want a particular collation for tempdb, you would choose the right collation to begin with for the instance when you are doing the install. However, in our case, we were doing some benchmarks for a client of ours and this requirement was raised pretty late in the game and the above method worked out great for us. In real life scenario, this would have been a bit more cumbersome since the model database could have other settings/objects etc. which would then need to be re-created after the restore.

9 Responses to “Changing the collation of the tempdb database”

  1. […] Changing Tempdb collation – our blog post here. […]

  2. Paak said

    i faced this problem when i migrated my sql 2000 db to a new server. Restoring my model DB from the old server resolved my problem. Your solution is just superb and absolutely fantastic. God richly bless you.

  3. Peter said

    I followed your instructions and was able to run a query which had failed before. However, when I tried to access the properties of any of the databases on my server, I got the following error message:

    Cannot show requested dialog.

    Additional information:

    – Cannot show requested dialog. (SqlMgmt)

    – – An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    – – – Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation. (Microsoft SQL Server, Error: 468)

    Have you ever run into this?

  4. Yes Peter … this means that your user database collation “Latin1_General_CI_AS” is not matching with the server collation (used by the system databases as well): SQL_Latin1_General_CP1_CI_AS.

    • jack said

      Is there a way to get rid of this error message? I was i this mess and your solution definitely helpled me to take care of this issue. However after doing this people can’t right click on the database and view the properties. Is there any way so that when people right click on the database they can view the properties after you change the collation for tempdb?

      • sqldba346 said

        I did the same thing. I had restored model db from other instance, now I am not able to see properties on individual user as well as sys databases. I had replaced resources db(took resourcedb from instance where i took backup of model.) the best practice to rebuild master database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: