SQL Server :- Error – “Cannot resolve the collation conflict between “Latin1_General_100_BIN2” and “Latin1_General_CI_AS” in the equal to operation”

Background

Error during SQL Server Upgrade; specifically upgrade from MS SQL Server 2016 SP1 to Sp2.

 

Error


2018-05-18 22:27:31.36 spid4s      Creating procedure sp_sqlagent_get_perf_counters...
2018-05-18 22:27:31.37 spid4s      Error: 468, Severity: 16, State: 9.
2018-05-18 22:27:31.37 spid4s      Cannot resolve the collation conflict between "Latin1_General_100_BIN2" and "Latin1_General_CI_AS" in the equal to operation.
2018-05-18 22:27:31.38 spid4s      Error: 912, Severity: 21, State: 2.
2018-05-18 22:27:31.38 spid4s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2018-05-18 22:27:31.38 spid4s      Error: 3417, Severity: 21, State: 3.
2018-05-18 22:27:31.38 spid4s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2018-05-18 22:27:31.38 spid4s      SQL Server shutdown has been initiated
2018-05-18 22:27:31.39 spid4s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This

Troubleshooting

Error Text

The key error entry reads:

Cannot resolve the collation conflict between “Latin1_General_100_BIN2” and “Latin1_General_CI_AS” in the equal to operation.

Review Collation

Compare SQL Instance Collation against System Database Collation

SQL


; with cteCollationSQLInstance
(
      [name]
    , [collation]
)
as
(
    select 
          [name] 
            = cast
                (
                    serverproperty('servername')
                    as sysname
                )

        , [collation]
            = cast
              (
                serverproperty('collation')
                   as sysname
              )

)

, cteCollationDatabaseSystem
(
      [dbid]
    , [name]
    , [collation]
)
as
(
    select 
              tblSD.[database_id]
            , tblSD.[name]
            , tblSD.collation_name

    from   sys.databases tblSD

    where  tblSD.[database_id] <= 4
)
select 
          [collationSqlInstance]	
            = cteCSI.[collation]

        , [dbid]
            = cteCDS.[dbid]

        , [database]
            = cteCDS.[name]

        , [collationDatabase]
            = cteCDS.[collation]

from   cteCollationDatabaseSystem cteCDS

cross apply cteCollationSQLInstance cteCSI

 

Output

Remediation

Collation

Revert System Databases Collation to Match SQL Instance Collation

Outline

  1. Uninstall SQL Server Service Pack
    • If change was discovered post SQL Service Pack ( SP ) install, please remove SP
  2. SQL Instance Service
    • Stop SQL Server Instance
    • Start SQL Server Instance from command line issuing change collation
    • Wait for collation to be changed
    • Stop SQL Server Instance
    • Restart SQL Server Instance through Services Applet
  3. Review Collation Settings

Steps

Uninstall SQL Server Service Pack

Using Control Panel, Programs & Features, please uninstall Service Pack

Change Collation
Script

Script – Sample


rem set SQL Server Binary Folder
set _FLD="E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\"
 
rem Collation to Binary
set "_COLLATION=Latin1_General_100_BIN2"

rem Start SQL Server and pass in argument -q for new collation 
%_FLD%\sqlservr -m -T4022 -q%_COLLATION% 

Review Collation
Script – Sample


; with cteCollationSQLInstance
(
      [name]
    , [collation]
)
as
(
    select 
          [name] 
            = cast
                (
                    serverproperty('servername')
                    as sysname
                )

        , [collation]
            = cast
              (
                serverproperty('collation')
                   as sysname
              )

)

, cteCollationDatabaseSystem
(
      [dbid]
    , [name]
    , [collation]
)
as
(
    select 
              tblSD.[database_id]
            , tblSD.[name]
            , tblSD.collation_name

    from   sys.databases tblSD

    where  tblSD.[database_id] <= 4
)
select 
          [collationSqlInstance]	
            = cteCSI.[collation]

        , [dbid]
            = cteCDS.[dbid]

        , [database]
            = cteCDS.[name]

        , [collationDatabase]
            = cteCDS.[collation]

from   cteCollationDatabaseSystem cteCDS

cross apply cteCollationSQLInstance cteCSI

Output

Summary

There is so much we learnt through this exercise

  1. SQL Server Service Pack uninstall actually works
    • That it works on this occasion does not mean one should thread that road without very, very careful forethought, reason, and arguments
  2. Discovered unlisted SQL Server Error Message
    • Error 200
      • Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
      • sys.messages
        • Reviewed sys.messages and noticed 196, 197, 198, 199, 201, 202, 203, 204
        • Noticed that 200 is jumped over
  3. SQL Server Collation
    • Instance collation can be different than system’s database; specifically master database
      • Consider possible ramification

 

Listening

Listening to Sir Elton John

Sad Song
Link

SQL Server – Messages – Backup

Background

Reviewing SQL Server Agent jobs and came upon some messages that I wanted to be sure do not pose any risk.

 

SQL Server Agent

Backup Jobs

backupdatabaselogbrushed-up

 

Messages

The specific messages that we will be digging more into are:

  1. 3014
  2. 3211

 

What do the messages mean?

Code


set nocount on;

declare @language		   sysname
declare @languageID		   int	

declare @languageIDDefault int
declare @languageIDEnglish int

declare @sqlMessageID_3014 int
declare @sqlMessageID_3211 int

set @sqlMessageID_3014 = 3014
set @sqlMessageID_3211 = 3211

set @languageIDDefault = 0 
set @languageIDEnglish = 1033

set @language        = @@language

/*
	Message
		Executed as user: LABDOMAIN\sqlAgent. ...rcent processed. [SQLSTATE 01000] (Message 3211)  
		19 percent processed. [SQLSTATE 01000...  The step succeeded.
*/
if (@language is not null)
begin

	select @languageID	= tblSL.[langid]
	from   sys.syslanguages tbLSL
	where  tblSL.[name] = @language

end

if (
		(@languageID is null)
	)
begin

	set @languageID = @languageIDDefault

end

print 'Default Language ' + isNull(@language, '')
print 'Language ID ' + cast(isNull(@languageID, -1) as sysname)

select top 100 *
from   sys.messages tbLSM
where  (
			   (tblSM.language_id = @languageID)
			or (tblSM.language_id = @languageIDEnglish)
	   )		
and    tblSM.message_id	in
		 (
			  @sqlMessageID_3014		
			, @sqlMessageID_3211
		 )


 

Explanation

What we did is query the master.sys.messages table

We filtered on

  1. The messages ID
    • 3014
    • 3211
  2. And, passed along the language ID

 

Output

output-grid

 

Explanation

Message ID Severity  Interpretation
 3014 10  At the completion of backups, we are told how many pages were backed, how long it took, and throughput
 3211  10  When the stats option is passed, backup and restore commands render percentile status message on the progress

 

BTW, Severity 10 is an informational message as demonstrated here.

Database Engine Error Severities

levelsofseverity

 

Summary

If bothered by the 3211 message please remove the stats operand from the backup command.

Using the 3014 message, backup completions are logged to both the console and SQL Server’s errorlog.

Please enable the 3605 Trace Flag to suspend logging of backup completion to the errorlog.

It is not clear if one is able to dissuade logging to the console.