SQL Server – Changing Sql Instance Collation – via sqlservr/-q – Little Traps


In an earlier post we spoke about discovering that we can change our SQL Server Instance by restarting SQL Server with the /q option.

That post is here.


Real life experience

If truth be told, our first use-case was a recently installed SQL Server instance without any user databases.

In this post we will cover our experience with another newly built SQL Instance, but on which we will be restoring user database as part of a robust response to a server crash.

Our platform team raised up a new VM Instance, assigned server names and IP Addresses to it, and added it to our Active Directory team.

We installed SQL Server and applied the latest Service Packs.

During SQL Server install, upon being asked for the new server’s collation, we cheated and entered the Collation of another server from the same user community.

We went on to restore databases from the Production DB.  And, we knew that we had to re-align the SQL Server Logins.

We assessed each restored databases and ran a script that transverses the contextual database sysusers table and link each user to the login.

BTW, the SP applied against each user is the sp_change_users_login.

Applying that script unearth our misguidedness.



Error Image



Error Textual

Msg 468, Level 16, State 9L

Cannot resolve the collation conflict between Latin1_General_BIN and Latin1_General_100_BIN2 in the equal to operation. 


Little Traps

Knew right away that I had chosen the wrong collation during the install.

And, will have to transition over to the right collation.

There are a couple of little traps that one needs to look out for.


Read Only Databases

If we have read only databases on the SQL Instance, we can not continue.

Here is the error message.


Error Image


Error Text

2017-01-03 22:33:48.55 spid34s     [INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: [22]. Starting offline checkpoint worker thread on a hidden SOS scheduler.

2017-01-03 22:33:48.55 spid34s     CHECKDB for database 'ppsivr_20161214' finished without errors on 2016-12-28 03:04:32.197 (local time). This is an informational message only; no user action is required.

2017-01-03 22:33:48.55 spid8s      Warning ******************

2017-01-03 22:33:48.55 spid8s      Attempting to change default collation to Latin1_General_100_BIN2.

2017-01-03 22:33:48.55 spid8s      Error: 5804, Severity: 16, State: 1.

2017-01-03 22:33:48.55 spid8s      Character set, sort order, or collation cannot be changed at the server level because at least one database is not writable.
                                   Make the database writable, and retry the operation.

2017-01-03 22:33:48.55 spid8s      Error: 3416, Severity: 20, State: 1.

2017-01-03 22:33:48.55 spid8s      The server contains read-only files that must be made writable before the server can be recollated.

2017-01-03 22:33:48.55 spid8s      SQL Server shutdown has been initiated

2017-01-03 22:33:48.56 spid8s      SQL Trace was stopped due to server shutdown.
                                   Trace ID = '1'. This is an informational message only; no user action is required.



In Memory Databases

If we have in-memory databases on the SQL Instance, we also can not continue.

Here is the error message.


Error Image



Error Text

2017-01-03 23:07:47.03 spid8s       index restored for rbpivr1.gen_edi_stats_loc_time.
2017-01-03 23:07:47.09 spid8s       index restored for rbpivr1.br_logon.
2017-01-03 23:07:47.79 spid8s       index restored for rbpivr1.it_prp.
2017-01-03 23:10:04.93 spid8s       index restored for rbpivr1.event_log_bkp_chs.
2017-01-03 23:10:06.57 spid8s       index restored for rbpivr1.it_retire_election.
2017-01-03 23:10:06.61 spid8s       index restored for rbpivr1.gen_ua_appgroup.
2017-01-03 23:10:07.46 spid8s      Error: 41317, Severity: 16, State: 4.
2017-01-03 23:10:07.46 spid8s      A user transaction that accesses memory optimized tables or natively compiled procedures cannot access more than one user dat
abase or databases model and msdb, and it cannot write to master.
2017-01-03 23:10:09.54 spid8s      Error: 3434, Severity: 20, State: 1.
2017-01-03 23:10:09.54 spid8s      Cannot change sort order or locale. An unexpected failure occurred while trying to reindex the server to a new collation. SQL
 Server is shutting down. Restart SQL Server to continue with the sort order unchanged. Diagnose and correct previous errors and then retry the operation.
2017-01-03 23:10:09.54 spid8s      SQL Server shutdown has been initiated
2017-01-03 23:10:09.55 spid8s      SQL Trace was stopped due to server shutdown.
 Trace ID = '1'. This is an informational message only; no user action is required.




Validate that in-memory tables exists on the cited database, please issue a query against the sys.tables table and look for cases where the is_memory_optimized is 1.

use [--database=--]

		, tblSO.[name]
		, tblSO.is_memory_optimized
from   sys.tables tblSO

inner join sys.schemas tblSS

		on tblSO.[schema_id] = tblSS.[schema_id]

where  tblSO.[type] = 'U'

and   tblSO.is_memory_optimized = 1







Make a list of the identified databases, their and log files, detach the databases, change the collation, restart the SQL Instance, re-attach the detached databases.

Please keep in mind that you really need to write down the database names and make a good list of the datafiles, as you will need that information when trying to re-attach the databases.


5 thoughts on “SQL Server – Changing Sql Instance Collation – via sqlservr/-q – Little Traps

  1. Daniel, I ran into the In-Memory OLTP issue that you mention here and figured out what was causing it: auto-created statistics! If you delete all auto-created statistics on the in-memory tables, then this “-q” option will work on the in-memory tables :).

    I updated my post on the “sqlservr -q” method (the “Changing the Collation of the Instance…” link in the comment just above this one) to include this info. I also provide some T-SQL that will find and remove these auto-created statistics. Just search for “Fix for In-Memory OLTP Errors” in that post.

      • Yer quite welcome. I can’t remember everything that lead me to the auto-stats, but I think I was checking to see if I could use this approach to bypass the restriction on using the new UTF-8 collations with memory-optimized tables ( https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/#UpdateCTP21 ), and it let me change the collation a few times (I had forgotten about the potential to get this error). But then I got the error another time while it said it was rebuilding indexes on a table that I had removed the indexes from, so I started looking into what could be considered an index or at least updated, something that wasn’t really an index, or something that was different between the tables. The auto-stats were the only things on the table that had no indexes, and the other tables didn’t have any auto-stats, so I figured it couldn’t hurt to try removing them. Then I did a few more tests, making sure that when it broke, at least one table had at least one auto-statistic, and then removing it/them (and making no other changes) would get it working again.

Leave a Reply to SQL Server – Changing Sql Instance Collation – via sqlservr/-q | Learning in the Open Cancel reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s