List ROW_OVERFLOW_DATA pages for a specific table
sql-server add tag
Hannah Vernon (imported from SE) 
I'm attempting to get a list of pages for a table that has rows with ROW_OVERFLOW_DATA.  I can get the list of allocated pages from the undocumented DMV, `sys.db_db_database_page_allocations`, however, there appears to be no ROW_OVERFLOW_DATA pages listed in the output of that DMV.  Is there some other DMV that I simply cannot locate?

Minimal, complete, and (hopefully!) verifiable example:

    USE tempdb;
    
    IF OBJECT_ID(N'dbo.t', N'U') IS NOT NULL
    DROP TABLE dbo.t;
    GO
    
    CREATE TABLE dbo.t
    (
        rownum int NOT NULL IDENTITY(1,1)
            PRIMARY KEY CLUSTERED
        , on_row_data varchar(30) NOT NULL
            DEFAULT ('on_row_data')
        , off_row_data varchar(MAX) NOT NULL
            DEFAULT REPLICATE('A', 20000) --PLENTY BIG ENOUGH!
    ) WITH (DATA_COMPRESSION = NONE); --not compressing those pages!
    
    INSERT INTO dbo.t DEFAULT VALUES;
    
    DECLARE @ObjectID int = (SELECT o.object_id FROM sys.objects o WHERE o.name = 't');
    DECLARE @PageID int;
    DECLARE @PageTypeDesc varchar(100);
    
    SELECT FileID = dpa.allocated_page_file_id
        , PageID = dpa.allocated_page_page_id
        , PageTypeDesc = dpa.page_type_desc
    FROM sys.dm_db_database_page_allocations(DB_ID(), @ObjectID, NULL, NULL, 'DETAILED') dpa

Output looks like:

```
╔════════╦════════╦══════════════╗
║ FileID ║ PageID ║ PageTypeDesc ║
╠════════╬════════╬══════════════╣
║      1 ║   1598 ║ IAM_PAGE     ║
║      3 ║ 105368 ║ DATA_PAGE    ║
║      3 ║ 105369 ║ NULL         ║
║      3 ║ 105370 ║ NULL         ║
║      3 ║ 105371 ║ NULL         ║
║      3 ║ 105372 ║ NULL         ║
║      3 ║ 105373 ║ NULL         ║
║      3 ║ 105374 ║ NULL         ║
║      3 ║ 105375 ║ NULL         ║
╚════════╩════════╩══════════════╝
```

Which makes sense, other than the missing ROW_OVERFLOW_DATA page.  We have a single index allocation map page, and a full extents-worth of 8KB data pages, with only a single one of those pages actually allocated.

Similarly, if I use the undocumented `sys.fn_PhysLocCracker` function to show the page where each row exists, as in:

    SELECT *
    FROM dbo.t
    CROSS APPLY sys.fn_PhysLocCracker(%%PHYSLOC%%)

I only see the `DATA_PAGE` listed:

```
╔════════╦═════════════╦═════════════════════╦═════════╦═════════╦═════════╗
║ rownum ║ on_row_data ║     off_row_data    ║ file_id ║ page_id ║ slot_id ║
╠════════╬═════════════╬═════════════════════╬═════════╬═════════╬═════════╣
║      1 ║ on_row_data ║ AAAAAAAAAAAAAAAAAAA ║       3 ║  105368 ║       0 ║
╚════════╩═════════════╩═════════════════════╩═════════╩═════════╩═════════╝
```

Likewise, if I use `DBCC IND(database, table, index)` I only see the two pages listed:

    DBCC IND (tempdb, t, 1);

Output:

```
╔═════════╦═════════╦════════╦════════╦════════════╦═════════╦═════════════════╦═════════════════════╦════════════════╦══════════╦════════════╦═════════════╦═════════════╦═════════════╦═════════════╦══╗
║ PageFID ║ PagePID ║ IAMFID ║ IAMPID ║  ObjectID  ║ IndexID ║ PartitionNumber ║     PartitionID     ║ iam_chain_type ║ PageType ║ IndexLevel ║ NextPageFID ║ NextPagePID ║ PrevPageFID ║ PrevPagePID ║  ║
╠═════════╬═════════╬════════╬════════╬════════════╬═════════╬═════════════════╬═════════════════════╬════════════════╬══════════╬════════════╬═════════════╬═════════════╬═════════════╬═════════════╬══╣
║       1 ║    1598 ║ NULL   ║ NULL   ║ 2069582411 ║       1 ║               1 ║ 6989586877272752128 ║ In-row data    ║       10 ║ NULL       ║           0 ║           0 ║           0 ║           0 ║  ║
║       3 ║  105368 ║ 1      ║ 1598   ║ 2069582411 ║       1 ║               1 ║ 6989586877272752128 ║ In-row data    ║        1 ║ 0          ║           0 ║           0 ║           0 ║           0 ║  ║
╚═════════╩═════════╩════════╩════════╩════════════╩═════════╩═════════════════╩═════════════════════╩════════════════╩══════════╩════════════╩═════════════╩═════════════╩═════════════╩═════════════╩══╝
```


If I look at the actual page contents, using `DBCC PAGE`, it looks like I *still* don't see anything about which page contains the ROW_OVERFLOW_DATA - I'm sure it must be there, I probably just don't know what to look at:

    DBCC PAGE (tempdb, 3, 105368 , 3) WITH TABLERESULTS;

The results are too big to fit here, if I include the memory dump rows, but this is the header output:

```
╔══════════════╦════════════════════════════════╦═══════════════════════════════╦═══════════════════════════════╗
║ ParentObject ║             Object             ║             Field             ║             VALUE             ║
╠══════════════╬════════════════════════════════╬═══════════════════════════════╬═══════════════════════════════╣
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bpage                         ║ 0x000002431A8A2000            ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bhash                         ║ 0x0000000000000000            ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bpageno                       ║ (3:105368)                    ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bdbid                         ║ 2                             ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ breferences                   ║ 0                             ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bcputicks                     ║ 0                             ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bsampleCount                  ║ 0                             ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bUse1                         ║ 63172                         ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bstat                         ║ 0x10b                         ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ blog                          ║ 0x212121cc                    ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bnext                         ║ 0x0000000000000000            ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bDirtyContext                 ║ 0x000002435DA77160            ║
║ BUFFER:      ║ BUF @0x000002437E86D5C0        ║ bstat2                        ║ 0x0                           ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_pageId                      ║ (3:105368)                    ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_headerVersion               ║ 1                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_type                        ║ 1                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_typeFlagBits                ║ 0x0                           ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_level                       ║ 0                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_flagBits                    ║ 0xc000                        ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_objId (AllocUnitId.idObj)   ║ 3920762                       ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_indexId (AllocUnitId.idInd) ║ 512                           ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ Metadata: AllocUnitId         ║ 144115445026914304            ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ Metadata: PartitionId         ║ 6989586877272752128           ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ Metadata: IndexId             ║ 1                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ Metadata: ObjectId            ║ 2069582411                    ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_prevPage                    ║ (0:0)                         ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_nextPage                    ║ (0:0)                         ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ pminlen                       ║ 8                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_slotCnt                     ║ 1                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_freeCnt                     ║ 66                            ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_freeData                    ║ 8124                          ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_reservedCnt                 ║ 0                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_lsn                         ║ (36:47578:1)                  ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_xactReserved                ║ 0                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_xdesId                      ║ (0:0)                         ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_ghostRecCnt                 ║ 0                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ m_tornBits                    ║ 0                             ║
║ PAGE HEADER: ║ Page @0x000002431A8A2000       ║ DB Frag ID                    ║ 1                             ║
║ PAGE HEADER: ║ Allocation Status              ║ GAM (3:2)                     ║ ALLOCATED                     ║
║ PAGE HEADER: ║ Allocation Status              ║ SGAM (3:3)                    ║ NOT ALLOCATED                 ║
║ PAGE HEADER: ║ Allocation Status              ║ PFS (3:105144)                ║ 0x40 ALLOCATED   0_PCT_FULL   ║
║ PAGE HEADER: ║ Allocation Status              ║ DIFF (3:6)                    ║ NOT CHANGED                   ║
║ PAGE HEADER: ║ Allocation Status              ║ ML (3:7)                      ║ NOT MIN_LOGGED                ║
║ PAGE HEADER: ║ Slot 0 Offset 0x60 Length 8028 ║ Record Type                   ║ PRIMARY_RECORD                ║
║ PAGE HEADER: ║ Slot 0 Offset 0x60 Length 8028 ║ Record Attributes             ║  NULL_BITMAP VARIABLE_COLUMNS ║
║ PAGE HEADER: ║ Slot 0 Offset 0x60 Length 8028 ║ Record Size                   ║ 8028                          ║
╚══════════════╩════════════════════════════════╩═══════════════════════════════╩═══════════════════════════════╝
```
Top Answer
Josh Darnell (imported from SE) 
Your demo is being hit by a [limitation of REPLICATE][1]:

> If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

If I do this:

    INSERT INTO dbo.t (off_row_data) VALUES (REPLICATE(CAST('A' as varchar(max)), 20000));

And then run your DMV query from above against dm_db_database_page_allocations, I get pages with a PageTypeDesc of `TEXT_MIX_PAGE`.

I can then run DBCC PAGE with trace flag 3604 enabled in order to see the details of that off-row page:

    DBCC TRACEON (3604);
    GO
    DBCC PAGE (TestDB, 1, 20696 , 3) -- your page will be different :)

The output is large, but near the beginning you'll see:

    Blob row at: Page (1:20696) Slot 0 Length: 3934 Type: 3 (DATA)

And then, you know, a bunch of A's.

[1]: https://docs.microsoft.com/en-us/sql/t-sql/functions/replicate-transact-sql?view=sql-server-2017
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.