The Curious Case of… object ID 99

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

Earlier today I was answering a question on the #sqlhelp tag on Twitter and I mentioned using object ID 99 as a value, because SQL Server will never set a table to be object ID 99. And so I thought it would make a good topic for a quick blog post.

So what is object ID 99? It’s a reserved object ID that the Storage Engine uses for any data file pages that are part of the allocation system and not owned by a real table. The list of such pages includes things like file header pages, PFS pages, GAM pages, the boot page, and so on – the pages used by the allocation system to track what’s happening at the database level. It doesn’t include IAM pages, as those are part of a table.

You can read more about the various allocation pages in my post Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps.

Let’s look at the page header of a GAM page:

DBCC TRACEON (3604);
DBCC PAGE (N'master', 1, 2, 0); -- dbname, file ID, page ID, dump style
-- Page (1,2) is the first GAM page in a data file
-- Dump style 0 = just the page header
GO
Page @0x00000002F83A6000

m_pageId = (1:2)                    m_headerVersion = 1                 m_type = 8
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 90
m_slotCnt = 2                       m_freeCnt = 6                       m_freeData = 8182
m_reservedCnt = 0                   m_lsn = (5081:482:5)                m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 39122617
DB Frag ID = 1                      

You can see that the m_objId is 99. Since SQL Server 2005, allocation pages are the only ones where the m_objId is the actual object ID. In all other cases, the m_objId and m_indexId are derived from the allocation unit ID that the page is allocated to, and the allocation unit ID reported by DBCC PAGE is calculated as:

(m_objId << 16) | (m_indexId << 48)

(where << means left-shift and | means logical-OR)

You can read more about this in my post Inside the Storage Engine: How are allocation unit IDs calculated?

The only other ‘not real’ object ID you might see is a value of 0 being reported by DBCC CHECKDB, when it can’t figure out which object a page really belongs to – see Disaster recovery 101: Object ID 0, index ID -1, partition ID 0 for more details.

One thought on “The Curious Case of… object ID 99

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.