SQL Server – Database Files – Minimum File Size Via “DBCC FILEHEADER”

Background

In our last post, we spoke of using “DBCC PageHeader” to fetch minimum file sizes.

And, as closing, we touched on the fact that the code pasted does not work for log files, just data files.

Code

Syntax


dbcc fileheader
(
      {'dbname' | dbid}
    , filenum

)

Explanation

  1. DBCC FileHeader
    • Database
    • FileID

SQL


set nocount on;
go

set XACT_ABORT on
go

declare @database		    sysname
declare @dbid			    int

declare @fileID			    int
declare @fileIDAsString		varchar(255)

declare @fileSymbolicName   sysname
declare @filePhysicalName	nvarchar(600)

declare @sql				nvarchar(255)

declare @FORMAT_SQL			varchar(255)

declare @tblFileHeader Table
(

      [RecoveryUnitID]			smallint
    , [FileId]					smallint
    , [LogicalName]				sysname
    , [BindingID]				uniqueidentifier
    , [FileGroup]				smallint

    , [Size]					bigint
    , [MaxSize]					bigint
    , [MinSize]					bigint
    , [UserShrinkSize]			varchar(255)

    , [Growth]					varchar(255)

    , [BackupLSN]				decimal(38, 0)
    , [RedoStartLSN]			decimal(38, 0)
    , [FirstLSN]				decimal(38, 0)
    , [MaxLSN]					decimal(38, 0)
    , [FirstUpdateLSN]			decimal(38, 0)
    , [CreateLSN]				decimal(38, 0)

    , [SectorSize]				smallint
    , [ActualSectorSize]		smallint

    , [RecoveryForkGUID]		uniqueIdentifier
    , [RecoveryForkLSN]			decimal(38, 0)

    , [DifferentialBaseLSN]		decimal(38, 0)
    , [DifferentialBaseGuid]	uniqueidentifier

    , [Status]					smallint
    , [RestoreStatus]			smallint

    , [ReadOnlyLsn]				decimal(38, 0)
    , [ReadWriteLsn]			decimal(38, 0)
    , [MaxLsnBranchId]			uniqueIdentifier

    , [RedoTargetPointLsn]		decimal(38, 0)
    , [RedoTargetPointGuid]		uniqueIdentifier

    , [RestoreDiffBaseLsn]		decimal(38, 0)
    , [RestoreDiffBaseGuid]		uniqueIdentifier

    , [RestorePathOriginLsn]	decimal(38, 0)
    , [RestorePathOriginGuid]	uniqueIdentifier

    , [OldestRestoreLsn]		decimal(38, 0)

    , [sizeInKB]
        as (
                [Size] * 8
            )

    , [sizeInMB]
        as
             (
                [Size] * 8
             )
             / ( 1024)

    , [MinSizeInMB]
        as
             (
                [MinSize] * 8
             )
             / ( 1024)

)

set @FORMAT_SQL = 'DBCC FILEHEADER(''%s'',%s) with tableresults, no_infomsgs';

-- exec sp_helpdb 'skySync'
set @database = 'skySync';

set @fileID = 1
set @fileID = 2

set @dbid = db_id(@database);

select
          @fileSymbolicName = tblSMF.[name]
        , @filePhysicalName = tblSMF.physical_name

from   sys.master_files tblSMF 

where  tblSMF.database_id = @dbid

and    tblSMF.[file_id] = @fileID

set @fileIDAsString = cast(@fileID as varchar(10));

exec master..xp_sprintf
            @sql output
        , @FORMAT_SQL
        , @database
        , @fileIDAsString

DBCC TRACEON(3604) with no_infomsgs;

print @sql

insert into @tblFileHeader
exec(@sql)

-- exec sp_helpdb [model]
select
          [database] = @database
        , [fileSymbolicName] = @fileSymbolicName
        , [filePhysicalName] = @filePhysicalName

        , tblFH.[Size]
        , tblFH.[SizeInMB]

        , tblFH.[MinSize]
        , tblFH.[MinSizeInMB]

from   @tblFileHeader tblFH

DBCC TRACEOFF(3604) with no_infomsgs

Output

skySync_20180711_0140PM

Credits

Crediting sqllity

DBCC FILEHEADER: Reading the File Header Page
Link