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

SQL Server – Database Compatibility Level

Background

Upgrading quite a bit of SQL Server Instances.

One of the areas to keep an eye on is the compatibility level of individual databases within each Instance.

Code

Read Compatibility Level

Credit

Crediting Nick Kavadias ( Link ) for his response on Stack Overflow:

How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
Link

SQL



/*
    How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
    https://stackoverflow.com/questions/1501596/how-to-check-sql-server-database-compatibility-after-sp-dbcmptlevel-is-deprecate

*/
select 

              [database] 
                = tblSD.[name]

            , [compatibilityLevel]
                = tblSD.[compatibility_level]

            , [version] = 
                    CASE [compatibility_level]
                        WHEN 65  THEN 'SQL Server 6.5'
                        WHEN 70  THEN 'SQL Server 7.0'
                        WHEN 80  THEN 'SQL Server 2000'
                        WHEN 90  THEN 'SQL Server 2005'
                        WHEN 100 THEN 'SQL Server 2008/R2'
                        WHEN 110 THEN 'SQL Server 2012'
                        WHEN 120 THEN 'SQL Server 2014'
                        WHEN 130 THEN 'SQL Server 2016'
                        WHEN 140 THEN 'SQL Server 2017'
                    END

from sys.databases tblSD

order by 
        tblSD.[name]

Set Compatibility Level

Legacy

sp_dbcmptlevel

Syntax


exec master..sp_dbcmptlevel 
         @dbname = @name 
       , @new_cmptlevel = @version 

Sample


exec master..sp_dbcmptlevel 
         @dbname = 'hrdb' 
       , @new_cmptlevel = 130

Modern

Alter Database / Set Compatibility Level

Syntax


alter database [db-name]
	set COMPATIBILITY_LEVEL = [compatibilityLevel]

Sample


alter database [hrdb]
	set COMPATIBILITY_LEVEL = 130

References

  1. Alter Database
    • ALTER DATABASE (Transact-SQL) Compatibility Level
      • Docs / SQL / T-SQL / Statements
        Link
    • sp_dbcmptlevel (Transact-SQL)
      • Docs / SQL / Relational databases / System stored procedures
        Link