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);
GO

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%);
GO

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.

USE FileHeaderTest;
GO

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.

While we were at TechEd in June, Kimberly and I participated in an hour-long discussion panel (Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy) that was video-taped by the TechEd Online folks. It's now been edited and is available for download/viewing. We cover everything from requirements analysis to technology details in SQL Server 2008. The other panel members were Satya Jayanty, Allan Hirt, Kevin Farlee, and Amit Bansal.

You can browse the various online videos at http://technet.microsoft.com/en-us/events/teched/cc561184.aspx or go straight to the panel discussion video here.

Enjoy!

Now that I've done all the business-related blog posts, back to the good stuff to stop people complaining!

Something that's cropped up a few times over the summer so far is people trying to repair boot page corruptions.

First off, what's a boot page? Every database has a single page that stores critical information about the database itself. It's always page 9 in file 1 (the first file in the PRIMARY filegroup). You can examine the page using DBCC PAGE and it will interpret all the fields for you, but there's another command, DBCC DBINFO, that also dumps all this info (in fact the DBCC PAGE code calls the same underlying dumping code). This command is undocumented and unsupported but widely known and 'documented' in lots of places on the web - given that it uses the same code as DBCC PAGE, it's just as safe to use IMHO.

So what's on the boot page?

DBCC DBINFO ('BootPageTest');
GO

DBINFO STRUCTURE:

DBINFO @0x5BF6EF84

dbi_dbid = 19                        dbi_status = 65536                   dbi_nextid = 2073058421
dbi_dbname = BootPageTest            dbi_maxDbTimestamp = 2000            dbi_version = 611
dbi_createVersion = 611              dbi_ESVersion = 0                   
dbi_nextseqnum = 1900-01-01 00:00:00.000                                  dbi_crdate = 2008-07-10 15:53:18.843
dbi_filegeneration = 0              
dbi_checkptLSN

m_fSeqNo = 41                        m_blockOffset = 29                   m_slotId = 55
dbi_RebuildLogs = 0                  dbi_dbccFlags = 2                   
dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000                          
dbi_dbbackupLSN

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0

dbi_oldestBackupXactLSN

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_LastLogBackupTime = 1900-01-01 00:00:00.000                          
dbi_differentialBaseLSN

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0

dbi_createIndexLSN

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0

dbi_versionChangeLSN

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_familyGUID = a4e88c13-b4cf-4320-834e-92b237244d4b                    
dbi_recoveryForkNameStack

entry 0

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
m_guid = a4e88c13-b4cf-4320-834e-92b237244d4b                            

entry 1

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
m_guid = 00000000-0000-0000-0000-000000000000                            
dbi_differentialBaseGuid = 00000000-0000-0000-0000-000000000000           dbi_firstSysIndexes = 0001:00000014
dbi_collation = 872468488            dbi_category = 0                     dbi_maxLogSpaceUsed = 231936
dbi_localState = 0                   dbi_roleSequence = 0                
dbi_failoverLsn

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0

dbi_dbmRedoLsn

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0

dbi_dbmOldestXactLsn

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_dbMirrorId = 00000000-0000-0000-0000-000000000000                    
dbi_pageUndoLsn

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_disabledSequence = 0            
dbi_dvSplitPoint

m_fSeqNo = 0                         m_blockOffset = 0                    m_slotId = 0
dbi_CloneCpuCount = 0                dbi_CloneMemorySize = 0             
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

There's all kinds on interesting things in there, for instance:

• dbi_version and dbi_createversion: the physical version number of the database (and when it was created). See question 1 in the August 2008 SQL Q&A column in TechNet Magazine for an explanation (see here).
• dbi_RebuildLogs: a count of the number of times the transaction log has been rebuilt for the database. PSS can use this to tell whether corruption problems could have been caused by DBAs rebuilding the log
• dbi_dbccLastKnownGood: the completion time of the last 'clean' run of DBCC CHECKDB
• a bunch of different LSNs related to checkpoint, backups, database mirroring
• dbi_LastLogBackupTime: self-explanatory
• dbi_differentialBaseGuid: the GUID generated by the last full database backup. Differential backups can only be restored on top of a matching full backup - so an out-of-band full backup could screw-up your disaster recovery - see this blog post for more info.

Now, what about if this page is corrupt in some way? I corrupted the BootPageTest database to have a corrupt boot page. Let's see what happens:

USE BootPagetest;
GO

Msg 913, Level 16, State 4, Line 1
Could not find database ID 19. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.

Well, almost... Kimberly and I have agreed to co-author the upcoming SQL Server 2008 Internals book with our good friend Kalen Delaney. This is the 2008 evolution of Kalen's Inside SQL Server series of books and will be published by MS Press around February 2009. The other authors are (also our good friends) Adam Machanic and Conor Cunningham.

Kimberly's going to write the chapter on Index Internals and I'm going to write the chapter on DBCC Internals. This is very exciting as these are our respective favorite subjects, as you probably already know - these won't be short chapters

It's going to be a busy rest of the year - phew!

PS Next year we have plans to write a book ourselves - watch this space...

September, October, and November are going to be a whirlwind this year - after 3 weeks in the UK and Ireland in September, teaching and the San Francisco Power Workshop in October, we have three back-to-back conference weeks in Barcelona, Las Vegas, and back to Seattle! Hey - who booked that schedule?!?!?! Well, at least it helps us keep our top frequent-flyer status on United

Here's the line-up - see our Upcoming Events page for all the abstracts (including those from Bob Beauchemin and Stacia Misner too).

TechEd EMEA IT Pro, November 3-7, Barcelona, Spain

• We're still working with the TechEd team to finalize the content we'll be delivering but it's looking like the same three sessions from TechEd US, plus a bunch of new ones and Instructor-Led-Labs
• Sessions (at least):
  • Are Your Indexing Strategies Working?
  • Corruption Survival Techniques: From Detection to Recovery
  • Essential Database Maintenance

SQL Server Connections Fall, November 9-14, Las Vegas, USA

• This is the second of the twice-yearly SQL Connections conferences that Kimberly and I Co-Chair
• Workshops:
  • November 9: Pre-pre-con: Database Best Practices for the Involuntary DBA
  • November 10: Pre-con: Relational Data Warehousing: Leveraging Key Features of SQL Server 2005/2008
• Sessions:
  • Index Internals and Usage
  • Essential Database Maintenance
  • DBCC CHECKDB: The Definitive Guide
  • Follow the Rabbit: Interactive Q&A on Database Maintenance

PASS Community Summit 2008, November 17-21, Seattle, USA

• As unbelievable as this may be, I've *never* been to PASS before, even in the few times it was in Seattle - something always conflicted. Kimberly hasn't been since 2005 so it'll be cool for us both to be there this year.
• Workshop:
  • November 18: Database Maintenance: From Planning to Practice to Post-Mortem
• Spotlight Session:
  • Corruption Survival Techniques: From Detection to Recovery

People have been complaining that I've stopped blogging so much - vacation folks, vacation! Today I've got a few class and conference posts to get through and then I'll get back to the technical posts.

We're doing a 2.5 day public class based on the SQL Server 2008 material we developed earlier this year. This will be part of a larger conference being hosted by Dev Connections in San Francisco, USA. Our workshop will run October 6^th through 8^th.

You can register and get more details at http://www.devconnections.com/SFWorkshops/default.asp?s=127.

Here's the abstract:

SQL Server 2008 offers an impressive array of capabilities for professional developers that build upon key innovations introduced in SQL Server 2005. The use of many of these will have manageability and infrastructure implications for a database—and hence the DBA! There are also enhancements to existing high-availability technologies, plus a variety of significant new tools to aid in managing performance, scalability, administration, and troubleshooting. This workshop helps you understand how to exploit the new toolset and how to manage a database that makes use of the new features in SQL Server 2008. The multi-day format of this event allows us to explore each feature in more detail, with more in-depth demonstrations and labs.

Topics covered include:

• Availability Enhancements
  • Database Mirroring
  • Backup Compression
  • Peer-to-Peer Replication
• Security Enhancements
  • Transparent Data Encryption
  • Extensible Key Management
  • All Actions Audited
• Policy-Based Management and Multi-Server Administration
• Troubleshooting and Throttling
  • Resource Governor
  • Extended Events
• New Development Technologies
  • Spatial Indexes
  • Sparse Columns
  • Filtered Indexes and Statistics
  • Change Tracking and Change Data Capture
  • FILESTREAM
• Performance Data Collection
• Scalability Enhancements
  • Data Compression
  • Partition-Level Lock Escalation

This workshop runs Oct 6 (9am - 4pm), Oct 7 (9am - 4pm), Oct 8 (9am - 12pm).

Well, we're just back from vacation (photo blog post to follow) and I've heard that the feature article on Effective Database Maintenance I wrote for the August issue of TechNet Magazine is live on the web. It also includes a 5 minute long screencast I recorded where I demo the effect of database shrink on index fragmentation.

You can get to the article at http://technet.microsoft.com/en-us/magazine/cc671165.aspx. The topics covered are:

• Managing data and transaction log files
• Eliminating index fragmentation
• Ensuring accurate, up-to-date statistics
• Detected corrupted database pages
• Establishing an effective backup strategy

It's written around 2-300 level and presents a good overview (well, at least I think so ) of the concepts involved.

Also, the August SQL Q&A column is available at http://technet.microsoft.com/en-us/magazine/cc671180(TechNet.10).aspx. This month's topics on the web (more in the print magazine) are:

• Database version changes with upgrades
• Benefits of partitioning
• Consistency checking options for VLDBs

Enjoy!