SQL Server – DBCC Info

Background

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

That post is here.

 

DBCC DBINFO

Introduction

DBCC DBINFO offers concise metadata at the database level.

 

SYNTAX

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

 

Sample

ROWS

Display result as rows

Query


dbcc dbinfo with tableresults, no_infomsgs

Output

 

COLUMNS

Query



set nocount on
go

set XACT_ABORT on
go


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
        (
              [parentObject] 
            , [object]		 
            , [field]		 
            , [sequenceNbr]
        )
)

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

set @servername = cast(
                        serverproperty('servername')
                        as sysname
                      )

set @dbid = db_id()

set @dbname = db_name()

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

select *

from   @tblDBInfo

order by 
        [field] asc

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

)
, cteDBInfo
(
      [servername]
    , [dbid]
    , [Field]
    , [VALUE]
)
as
(

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

    from   @tblDBInfo tblDBI

    where  tblDBI.Field in
            (
                  'dbi_dbccLastKnownGood'
                , '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
as
(
    select 
              cteDBI.[serverName]
            , cteDBI.[dbid]
            , cteDBI.[dbi_dbccLastKnownGood]
            , [dbi_version]
            , [dbi_createVersion]
            , [dbi_LastLogBackupTime]
            , [dbi_crdate]
            , [dbi_dbname]
            , [dbi_dbid]
            , [dbi_cmptlevel]	

    from   cteDBInfo cteDBI

    PIVOT
        (
            MAX(VALUE)
    
            FOR [Field] in
            (
                  [dbi_dbccLastKnownGood]
                , [dbi_version]
                , [dbi_createVersion]
                , [dbi_LastLogBackupTime]
                , [dbi_crdate]
                , [dbi_dbname]
                , [dbi_dbid]
                , [dbi_cmptlevel]
            )

        ) cteDBI

)

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

from   ctePivot cteDBI


Output

Summary

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”

Background

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

 

Error Message

Textual

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

 

Remediation

Specification

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

 

Code


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

 

Collations

Collation Meaning
Latin1_General_CI_AS Latin Case Insensitive
Latin1_General_BIN2 Binary

 

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

Preface

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

Error Image

cannotresolvecollationconflict

 

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

Error Image

readonlydb-20170103-1037pm

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

Error Image

memoryoptimizeddb_20170103_1112pm

 

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

Code

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=--]
go


select 
		  tblSS.[name]
		, 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

 

Output

validateinmemorytablesexist

 

 

Remediate

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

Background

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

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….

 

Steps

Outline

  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

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]

Code

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% 
Output

indexrestored-defaultcollationwaschanged-0516pm

Explanation
  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.

Output

commandlinearguments

See -q is not listed.

 

Crediting

Crediting Douglas P. Castilho
Changing SQL Server Collation After Installation
Link

 

Additional Reading

  1. Pieter Vanhove
    • -m Startup Parameter issues
      Link

 

References

  1. Database Engine Service Startup Options

 

Addendum

Here are some follow-up posts:

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