Home » Blog » DBCC FILEHEADER: Reading the File Header Page

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:

[sql] DBCC TRACEON(3604);
DBCC PAGE(0,1,0,3);
[/sql]

Below is the beginning of the page header for this 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:

[sql] DBCC FILEHEADER(0,1);
[/sql]

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:

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.