SQL Server – DBCC Info


A few posts back we spoke about “SQL Server – Mapping DBCC Commands to Dynamic Management Views ( DMV )“.

That post is here.




DBCC DBINFO offers concise metadata at the database level.



Its syntax is elementary and here it is “DBCC dbinfo with tableresults“.




Display result as rows


dbcc dbinfo with tableresults, no_infomsgs





set nocount on


declare @tblDBInfo TABLE
      [parentObject] sysname not null

    , [object]		 sysname not null

    , [field]		 sysname not null

    , [sequenceNbr]  smallint 
                     identity(1,1) not null

    , [value]        nvarchar(4000) not null

    , primary key
            , [object]		 
            , [field]		 
            , [sequenceNbr]

declare @servername sysname
declare @dbid		int
declare @dbname		sysname

set @servername = cast(
                        as sysname

set @dbid = db_id()

set @dbname = db_name()

insert into @tblDBInfo
    , [object]		
    , [field]		
    , [value]       
exec ('dbcc dbinfo with tableresults, no_infomsgs')

select *

from   @tblDBInfo

order by 
        [field] asc

; with cteExclusion
    , [Field] 
           2 as [dbid]
        , 'dbi_dbccLastKnownGood' as [Field]

, cteDBInfo
    , [dbid]
    , [Field]
    , [VALUE]

            , @dbid
            , tblDBI.Field
            , tblDBI.[VALUE]

    from   @tblDBInfo tblDBI

    where  tblDBI.Field in
                , 'dbi_version'
                , 'dbi_createVersion'
                , 'dbi_LastLogBackupTime'
                , 'dbi_crdate'
                , 'dbi_dbname'
                , 'dbi_dbid'
                , 'dbi_cmptlevel'

    and not exists
            select 1
            from   cteExclusion cteEX
            where  cteEX.[dbid] = @dbid
            and    cteEX.Field = tblDBI.[Field]   


, ctePivot
            , cteDBI.[dbid]
            , cteDBI.[dbi_dbccLastKnownGood]
            , [dbi_version]
            , [dbi_createVersion]
            , [dbi_LastLogBackupTime]
            , [dbi_crdate]
            , [dbi_dbname]
            , [dbi_dbid]
            , [dbi_cmptlevel]	

    from   cteDBInfo cteDBI

            FOR [Field] in
                , [dbi_version]
                , [dbi_createVersion]
                , [dbi_LastLogBackupTime]
                , [dbi_crdate]
                , [dbi_dbname]
                , [dbi_dbid]
                , [dbi_cmptlevel]

        ) cteDBI


        , cteDBI.[dbid]
        , cteDBI.[dbi_dbccLastKnownGood]
        , [dbi_version]
        , [dbi_createVersion]
        , [dbi_LastLogBackupTime]
        , [dbi_crdate]
        , [dbi_dbname]
        , [dbi_dbid]
        , [dbi_cmptlevel]

from   ctePivot cteDBI



DBCC DBINFO has a wealth of information.

Nuggets include:

  1. Database Attributes
    • Compatibility level
    • Collation
    • Containment
    • Create Date
    • Database ID ( dbid )
    • Database name ( dbname )
    • MirrorId ( Mirrored database )
    • familyGUID ( Restored DB & Snapshot )
    • Safety ( Mirrored database )
    • Database Version
    • Database Create Version
  2. Operation Tracking
    • Last time dbcc checkdb was ran
    • Last time Transaction Log backup was taken

sqlservr.exe – Error – “Multiple instances of SQL server are installed on this computer. Renter the command, specifying the -s parameter with the name”


Trying to change collation from binary to something easier to work such as case-insensitive.


Error Message


Multiple instances of SQL server are installed on this computer. Renter the command, specifying the -s parameter with the name




Specify the Instance Name by utilizing MSSQLSERVER for the default instance; for named instance, please use the named instance name.



set "_FLD=E:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn"
set "_APP=sqlservr.exe"
set "_COLLATION=Latin1_General_CI_AS"
"%_FLD%\%_APP%" -s %_INSTANCE% -m -T4022 -q%_COLLATION%



Collation Meaning
Latin1_General_CI_AS Latin Case Insensitive
Latin1_General_BIN2 Binary


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.


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


Reviewing SQL Server Alwayson requirements and as should be expected Collation Uniformity is needed (at the SQL Instance level).

Keep in mind not just at the database level, but at Instance level.


Curious as to the means to change Collation.

Familiar with doing so via re-running setup.exe.

UnDocumented Method

Was fascinated that there appears to be an undocumented route.  Let us see how dark that road is….




  1. Stop the SQL Server Instance
  2. Connect to the box using the Account SQL Server is running under or try runas
  3. Launch Cmd.exe in Administrator mode
  4. Start SQL instance using “ -m -T4022 -T3659  -q[COLLATION]


Actual Steps

Stop the SQL Instance


Ensure you are SQL Server Account

Ensure that you are running as the SQL Server Account.

To do so try the methods listed below:

  1. Logoff and Logon
  2. Issue “runas /user <username>


Launch Shell in Administrator Mode

Launch cmd.exe in Administrator Mode


Start SQL Server with needed parameters


You need the following parameters

  1. Trace Flags
    • T4022
      • Skip Start-up procedures
    • T3659
      • Writes all log to errorlog
  2. Command Line Arguments
    • -q
      • -q and collation


Sample Code

Change to United Kingdom [UK]


set _FLD="D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\"

rem United Kingdom
set "_COLLATION=Latin1_General_CI_AS"

%_FLD%\sqlservr -m -T4022 -q%_COLLATION% 


  1. -q “Latin1_General_CI_AS”
    • Targeted Collation is mentioned at command line
  2. Attempting to change default collation to <target collation>
    • Attempting to change default collation to Latin1_General_CI_AS
  3. Index Restored ….
    • Index Restored for various tables
  4. Default Collation was successfully changed


sqlservr.exe options

Again, this is undocumented.

If one goes into the SQL Server Binary folder and issue “sqlservr.exe /?“, one will not see the -q option.



See -q is not listed.



Crediting Douglas P. Castilho
Changing SQL Server Collation After Installation


Additional Reading

  1. Pieter Vanhove
    • -m Startup Parameter issues



  1. Database Engine Service Startup Options



Here are some follow-up posts:

  1. SQL Server – Changing Sql Instance Collation – via sqlservr/-q – Little Traps
    Date Added :- 2017-Jan-8th