Following on from my previous post on boot pages and boot page corruption, I’ve been asked about file header pages – and I was already planning this post as the next in the series.
So what’s a file header page? Every data file in a database has the very first 8kb page (i.e. page 0 in the file) set aside as the place to store all the metadata info about the file. As with the boot page, you can look at the contents with DBCC PAGE and it will interpret all the fields for you, or you can use the DBCC FILEHEADER command, which does a better job. This is undocumented and unsupported, just like DBCC DBINFO for looking at the database boot page, but it’s been discussed and posted about on the Internet before so it’s existence is no secret.
The command take a database name or database ID plus the file ID to dump. Here I’ve created a database called FileHeaderTest and used SSMS with results-to-text, plus a bunch of editing of the results to make it blog-able:
DBCC FILEHEADER (‘FileHeaderTest’, 1);
FileId : 1
LogicalName : FileHeaderTest
BindingId : D30AE3EF-14A6-47D5-B267-96F38238D882
FileGroup : 1
Size : 152
MaxSize : -1
MinSize : 152
UserShrinkSize : -1
Growth : 128
BackupLSN : 0
RedoStartLSN : 0
FirstLSN : 0
MaxLSN : 0
FirstUpdateLSN : 0
CreateLSN : 0
SectorSize : 512
RecoveryForkGUID : 00000000-0000-0000-0000-000000000000
RecoveryForkLSN : 0
DifferentialBaseLsn : 19000000048800037
DifferentialBaseGuid : 279A8EF4-4431-4CA5-8939-F613E5BC3033
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
Lots of interesting stuff in here, such as:
BindingId: used to make sure a file is really part of this database
SectorSize: the disk sector size
Status: what kind of file and what state is it in (e.g. 2 = regular disk file)
Various sizes in number-of-8kb-pages (e.g. MaxSize of -1 means file growth is unlimited)
Growth: the number of pages to grow the file by if the 0x100000 bit is NOT set in the Status field. If it is set, the Growth is in percent.
And you can watch things change. For instance, if I change the file growth to 10%:
ALTER DATABASE FileHeaderTest MODIFY FILE (NAME = FileHeaderTest, FILEGROWTH = 10%);
And then dump the file header page contents again, the Status and Growth fields have changed to:
Growth : 10
Status : 1048578
So what if a file header page is corrupt? I corrupted the file header page of my database and then started up SQL Server.
Msg 945, Level 14, State 2, Line 1
Database ‘FileHeaderTest’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Let’s try EMERGENCY mode:
ALTER DATABASE FileHeaderTest SET EMERGENCY;
Msg 5172, Level 16, State 15, Line 1
The header for file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\FileHeaderTest.mdf’ is not a valid database file header. The PageAudit property is incorrect.
In this case, there’s nothing to do except restore from backups, and the database is inaccessible because the PRIMARY filegroup could not be brought online. If the corruption were in a file in a secondary filegroup, things are a little bit different. Now I’ve added a secondary filegroup with a single file (called CorruptFile) and corrupted it’s file header page. After starting up SQL Server we get the same behavior – but this time we can set the file to be offline and access the rest of the database. This is called partial database availability and works in Enterprise (and Developer) Edition only.
ALTER DATABASE FileHeaderTest MODIFY FILE (NAME = CorruptFile, OFFLINE);
Note that the only way to bring an offline file back online is to restore it from a backup – see this post from my old Storage Engine blog for more details. Another corruption that can only be repaired using backups…