How can I change collation on tempdb, master db without rebuild of other dbs?
Posted on 2007-11-30
I have an instance of sql-2005. All of the user db's were migrated from an sql-2000 instance, and they all have a collation of SQL_Latin1_General_CP1_CI_AS.
Yet the person who had previously set up this new instance had set it up with the collation Latin1_General_CI_AI.
Because of this I sometimes get collation errors, and from what I've read it's mostly due to sorting temp tables in the tempdb. So is is possible to change the tempdb collation, or is that based on the master or model db's collation? And if so, can I change either of those collations (without rebuilding all user dbs)?
Bottom line is I'm just looking for the easiest way to maintain the SQL_Latin1_General_CP1_CI_AS collation in my user db's and avoid the collation errors. I have a terabyte or so of user db's and I want to avoid the downtime of backup/restore of those just to change a collation on the instance. What's the best way to achieve this?