What is the Maximum Page Size in SQL Server 2000?


What is the Maximum Page Size in SQL Server 2000?


I have always read that 8060 bytes is the maximum size. This is stated over and over again in

Books Online, the MS site and numerous other sites, including this one. However a post in our forum

recently questioned this. I decided to verify the problem and do a little research.

The Problem

A reader posted the following question:

create table 
testtablesize (col1 varchar(8000), col2 varchar(35))
The command(s) completed successfully.
create table testtablesize (col1 varchar(8000), col2 
The total row size (8061) for table 'testtablesize' 
exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum 
number of bytes will not be added.
The maximum after splitting into two columns is 8035 Bytes
  8060 - 8035 = 25 Bytes
Is the 25 Bytes used for header information ? 

My Detective Work

I started researching this problem using Books Online and Inside SQL Server 2000 by Kalen Delaney,

which I keep by my desk. Whenever I have questions about the internals of SQL Server, this is usually

my first resource.

An 8kb page contains 8192 bytes. This is because each kb is 1024 bytes. The Microsoft documentation specifies

that a page header contains 96 bytes of overhead. This is for keeping track of the page within the system, kind of

like a File Allocation Table on your hard drive. This leaves 8096 bytes for data and

row offsets (pg 247, Inside SQL Server 2000). Supposedly this leaves the maximum size for

a single data row at 8060 bytes.

So why does the above table not get created?

I decided to post to Usenet (microsoft.public.sqlserver.server). I got a couple responses in a few

hours, but none provided an explanation (one person merely answered "overhead").

Meanwhile, I decided to look up some more information in Inside SQL Server.

I learned the following:

First, my DDL for testing:

create table MyTest
(	mychar char( 8000)
	, mychar2 char( 39)

Each row has a 2 byte offset (pg 248, Inside SQL Server 2000). For a single row on a page this is

1 row x 2 bytes/row = 2 bytes. This leaves 8094 bytes (8096 - 2) for data and row offsets, 8060 for

data (I would think).

For a variable length column (like a varchar column), there is 2 bytes, per varchar column.

However my table doesn't contain any variable length columns, so this does not apply.

I was somewhat confused by the explanations in both the book and BOL. I kept flipping between the

explanations of datatype storage (Chapter 12 in Inside SQL Server) and the architecture descriptions

of the pages in both BOL and Inside SQL Server. Finally, I decided to look at my table

in syscolumns. I found:

This shows that column 1 uses 8000bytes (size), with an offset of 4 (overhead). The offset is where the

data starts from the end of the overhead. Column 2 (since these are fixed size columns) starts at an offset of

8004 (4 bytes + 8000 for column 1) and has a size of 39 bytes.

This makes sense. One other thing to notice, sysindexes shows the minlen=8043. This implies, based

on the descriptions in my resources, that this row will be 8043 bytes in length, no matter what the data. If

there were variable length columns, then this would be the minimum if all variable length columns were NULL.

Inside SQL Server says that this minlen does not include the following:

  • 2 bytes for # of columns.
  • bytes needed for the null bitmap.

I assume this means the status bytes (2) and the basic overhead of 2 bytes for are included.

Earlier, the null bitmap is shown as 1 bit for each column, which means a maximum number of bytes

equal to (the # of columns/8). For me, this is a single byte (2 bits).

For my row, I should see the following overhead:

  • 1 byte - status bits
  • 1 byte - more status bits
  • 2 bytes representing the length of the fixed data (value for these 2 bytes is 8039)
  • 8039 bytes - for storing the fixed length data.
  • 2 bytes that represent the number of columns (value here is 2)
  • 1 byte to store the null bitmap (2 cols = 2 bits = 1 byte)
  • This is a total of 8046 bytes. Still far short of what we expect. So where are the other 14 bytes?

    At this point in my detective work, I was a little annoyed. In my mind, there should be 8096 total bytes on

    a page with 36 of these bytes used for overhead. Instead, the 8060 figure often quoted includes some

    overhead, so you cannot really create a row (excluding rows with BLOBs) with 8060 bytes.

    If I add a row to this table, then I can examine a page. Here is what I ran:

    insert MyTest values ('A', 'B')

    With a single row in a new table, the page data should be easy to decode.

    When I run:

    dbcc traceon( 3604)
    dbcc page( 8, 1, 150, 1 )
    dbcc traceoff( 3604)

    I get:

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    PAGE: (1:150)
    BUF @0x192ABAC0
    bpage = 0x38916000        bhash = 0x00000000        bpageno = (1:150)
    bdbid = 8                 breferences = 1           bstat = 0xb
    bspin = 0                 bnext = 0x00000000        
    Page @0x38916000
    m_pageId = (1:150)        m_headerVersion = 1       m_type = 1
    m_typeFlagBits = 0x0      m_level = 0               m_flagBits = 0x8000
    m_objId = 1531868524      m_indexId = 0             m_prevPage = (0:0)
    m_nextPage = (0:0)        pminlen = 8043            m_slotCnt = 1
    m_freeCnt = 48            m_freeData = 8142         m_reservedCnt = 0
    m_lsn = (2082:2096:1)     m_xactReserved = 0        m_xdesId = (0:0)
    m_ghostRecCnt = 0         m_tornBits = 0            
    Allocation Status
    GAM (1:2) = ALLOCATED     SGAM (1:3) = ALLOCATED    
    PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL   DIFF (1:6) = CHANGED
    ML (1:7) = NOT MIN_LOGGED 
    Slot 0, Offset 0x60
    Record Type = PRIMARY_RECORD                        
    Record Attributes =  NULL_BITMAP                    
    38916060:  1f6b0010  20202041  20202020  20202020 ..k.A           
    38916070:  20202020  20202020  20202020  20202020                 
    ... (lots of these rows)
    38917FA0:  20202020  20202042  20202020  20202020     B           
    38917FB0:  20202020  20202020  20202020  20202020                 
    38917FC0:  20202020  20202020  02202020      0000            ...
    Row - Offset              
    0 (0x0) - 96 (0x60)       
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    If you examine the header information, this shows 48 bytes free on this page. Remember that

    these are fixed columns, so where is the extra overhead?

    NOTE: There were lots of rows that contained 20202020 in the space where the ... is used. I removed them

    to save some space.

    I did some research on the www.insidesqlserver.com

    site and found the answer (which was not in the book). SQL Server sometimes moves rows. In

    moving rows, it places a pointer that points back to the original row location. To reserve

    space for this back pointer, 10 bytes are required. However, the space for the back pointer is

    a variable length field, so there are 4 bytes needed for this as overhead (2 bytes for the "column"

    and 2 bytes for the length of the variable length field). Thus, there are a minimum of 14 extra

    bytes required by each row that is not really documented. Not in BOL, not in Inside SQL Server.

    If you add these 14 bytes to the row size I calculated, 8046, then you get the 8060 that is

    the maximum row size. Tada!!!!


    This was not all that interesting, but I did learn more about internal storage. I was also

    more than slightly annoyed to learn about the 14 bytes for the back pointer. To me, since

    this is not space that is available for the row, it should not be included, or even

    documented as a possible row size. The maximum possible row size is in fact 8039. IMHO,

    Microsoft is incorrectly providing a specification.

    As always, I hope you learned something and I welcome feedback on this article using the

    "Your Opinion" button below. Please take a moment to also rate this article.

    Steve Jones

    ©dkRanch.net October 2001

    Return to Steve Jones Home



4.55 (11)




4.55 (11)