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.
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.
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.
Instead of trying to make sense of the output, there is an easier way, the also undocumented DBCC FILEHEADER command:
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.
|LogicalName||Image ©sqlity.net 2014|
The one thing you can see immediately is that I have not been very diligent backing up this database…
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.
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.