Changing MSDB Collation

I support an applicatoin where the vendor requires the use of the LATIN_1_GENERAL_BIN sort collation in their user databases. Origionally, when I installed the instance, I did not select this sort collation for the instance. I have since gone back and reinstalled SQL Server with the correct sort collation. After that, I restored the vendors databases from backups I took prior to reinstalling the instnace and they retained the LATIN_1_GENERAL_BIN collation (the correct one).

I also restored the MSDB database to get all of my jobs back. When I did this, MSDB took the old sort collation (SQL_LATIN_1_GENERAL_CPI_CI_AS). MSDB is the only database that has this collation and I'm getting errors in the agent log complaining of collation conflicts. I'm pretty sure the fact that the MSDB sort collation is different that all the other databases is the thing that's causing these error messages.

My question is this: Is there any way to change the sort collation of MSDB without reinstalling SQL server? This is a clustered environment and everytime I've reloaded SQL, it's been cumbersome and taken me at least 1 day's work.

Also, I know there's a rebuild master utility that will rebuild master, model and MSDB, but when I tried that last time it failed and I had to reinstall SQL anyway. Does anyone know of a way around this or have any tips on using the rebuildmaster utility in a clustered environment?

Many thanks,I've never tried this, and would STRONGLY urge you to make a backup before you try it.

You can change the default colation for a database using ALTER DATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_4e5h.asp). You can use DBCC REINDEX (http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_94mw.asp) to rebuild the indicies (to use the new collation).

My gut feeling is that this should work, but let me stress again: Don't try this without at least two good backups!

-PatP|||Thanks Pat, but I already tried this (after taking good backups) and it didn't work. SQL 2000 won't let you alter the collation of MSDB.

I've been doing some researching and I actually think I'm going to try to use the rebuild master utility again. I've found an article in TechNet that gives better instructions than the ones I had before.

Thanks for the input.

Originally posted by Pat Phelan
I've never tried this, and would STRONGLY urge you to make a backup before you try it.

You can change the default colation for a database using ALTER DATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_4e5h.asp). You can use DBCC REINDEX (http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_94mw.asp) to rebuild the indicies (to use the new collation).

My gut feeling is that this should work, but let me stress again: Don't try this without at least two good backups!

-PatP|||Rebuild master utility is the only tool availble to affect any changes after the installation.

By default master's collation will be affected to remaining system databases.|||See, I learned something today. Now I can go home!

-PatP