So another story coming… This one distrubs chronological order, as I have already posted a story about replication, which ultimately was caused by the actions depicted here.
A little background – there is a database which is about 10 years old, which had a refresh just recently. During development it turned out that in order to keep reporting working a collation change is required, mostly due to data interchange via linked servers. It was easier than to work with collation on a object level, instead of trying to identify all the queries and modify them to align collation. As you might expect, a simple default database collation change was not possible due to check constraints being dependent on text columns. I had to drop and recreate those check constraints after collation change, but then it also appeared to me that while applying database_default collation on columns in some tables it couldn’t be completed simply due to indexes on those columns. So those indexes had to be dropped prior to table alteration just to be recreated after that. A lot of scripting was involved and whole operation took about 8 hours to complete.
One more recollection – since it was in a virtual machine environment, low disk space occured in a meantime, so if you come across such massive collation change, keep in mind that a lot of disk space is required for logging. In this case I required about 4 times of database size for logging, not to mention backups before and after.