DBCC FILEHEADER: Reading the File Header Page

2014-05-21 - General, Series, SQL Server Internals, Storage Wednesday

Introduction

Every database file contains a single page that contains information about the file itself. It is called the file header page. The file header page is always the first page of any file, page 0.

Examining the File Header Page

Like any other page, using DBCC PAGE with type 3 (after turning trace flag 3604 on for the connection) is a good way to dive into the page's content:

DBCC TRACEON(3604);
DBCC PAGE(0,1,0,3);

Below is the beginning of the page header for this page.

DBCC PAGE for a File Header Page.

As you can see, the page type for the file header page is 15. If you run the statement yourself, you will notice that the output of DBCC PAGE for this page type is not as clean as we would like.

Introducing: DBCC FILEHEADER

Instead of trying to make sense of the output, there is an easier way, the also undocumented DBCC FILEHEADER command:

DBCC FILEHEADER(0,1);

DBCC FILEHEADER takes two parameters. The first is the database id or the database name. If you pass in 0 as in the example above, the current database is used. The second parameter is the file id, the same value you would use as second parameter for the DBCC PAGE command. DBCC FILEHEADER can be used for log files too. As log files are not organized in pages, DBCC PAGE fails if you attempt to access a log file with it.

DBCC FILEHEADER produces a single result set as output:

DBCC FILEHEADER in Action.

Most of the values deal with backup management but for example, the file name is in there too. Below is the complete list of values returned.

RecoveryUnitId 0
FileId 1
LogicalName Image ©sqlity.net 2014
BindingId 60371E59-22B9-412F-835C-8B88B1173F07
FileGroup 1
Size 7688
MaxSize -1
MinSize 392
UserShrinkSize -1
Growth 128
BackupLSN 0
RedoStartLSN 0
FirstLSN 0
MaxLSN 0
FirstUpdateLSN 0
CreateLSN 0
SectorSize 512
ActualSectorSize 512
RecoveryForkGUID 00000000-0000-0000-0000-000000000000
RecoveryForkLSN 0
DifferentialBaseLsn 285000000204700037
DifferentialBaseGuid 9C180429-584D-4F07-B5C4-9FB47480147D
Status 2
RestoreStatus 0
ReadOnlyLsn 0
ReadWriteLsn 0
MaxLsnBranchId 00000000-0000-0000-0000-000000000000
RedoTargetPointLsn 0
RedoTargetPointGuid 00000000-0000-0000-0000-000000000000
RestoreDiffBaseLsn 0
RestoreDiffBaseGuid 00000000-0000-0000-0000-000000000000
RestorePathOriginLsn 0
RestorePathOriginGuid 00000000-0000-0000-0000-000000000000
OldestRestoredLsn 0

The one thing you can see immediately is that I have not been very diligent backing up this database…

Backups, Anyone?

While we are talking about backups, there is one special behavior with file header pages that should not go unmentioned: If the file header page should ever become corrupt, SQL Server will refuse to even touch that file. While for other pages, you might be able to read "around" the problem by mounting the database in emergency mode and/or executing DBCC CHECKDB with one of the repair options, if the file header page becomes corrupt you are out of luck. The only way to recover from this situation is to restore a good and recent backup.

Summary

Every database file includes a file header. For data files, it is stored in a special page, the file header page. While you could use DBCC PAGE for file header pages, the DBCC FILEHEADER command offers a more readable output and has the added benefit to work with log files too.

Categories: General, Series, SQL Server Internals, Storage Wednesday
Tags: , , , , , ,

0 comments

Trackbacks

  1. […] PAGE command we have used with the other page types. I am not aware of any special command like DBCC FILEHEADER that would produce an easier to read output of the database boot page, but new undocumented DBCC […]