Cannot create a row of size XXXX which is greater than the allowable maximum row size of 8060



How SQL server stores of the size of the row is greater than 8060 bytes

Last year over TechNet some DBA ask me "How SQL server stores of the size of the row is greater than 8060 bytes", So thought to publish answer with more details here.


Q1.  how SQL server stores of the size of the row is greater than 8060 bytes?
------>from SQL 2005 onwards if row is greater than 8060 bytes then sql server use row-overflow page

Q2. Will the system take new page ?
-----> Yes it takes new pages as per requirement

3. How the system links two different pages ?
-----> To link these pages with object(table) and Iam page will be created for row-overflow
pages and this Iam page will be linked in Iam page chain for table

To prove same I am using code I got from Microsoft Press 2008 Internals


/*Create table to have data more that 8060 bytes */ 
CREATE TABLE dbo.hugerows
(
a varchar(3000),
b varchar(8000),
c varchar(8000),
d varchar(8000));

/*Inserting data much more that 8060 bytes*/

INSERT INTO dbo.hugerows
SELECT
REPLICATE('a', 3000),
REPLICATE('b', 8000),
REPLICATE('c', 8000),
REPLICATE('d', 8000);


/*To determine whether SQL Server is storing any data in row-overfl ow data pages for a
particular table*/
 
 
SELECT object_name(object_id) AS name,
partition_id,
partition_number AS pnum, rows,
allocation_unit_id AS au_id,
type_desc as page_type_desc,
total_pages AS pages
FROM
sys.partitions p JOIN sys.allocation_units a
ON
p.partition_id = a.container_id
WHERE
object_id=object_id('dbo.hugerows');


/* To determine what type of pages sql server have */
 
DBCC IND (test, hugerows, -1)



à Refer:  IAM_chain_type, PageType
/****************************************************/
Page types:
■ PageType = 1, Data page.
■ PageType = 2, Index page.
■ PageType = 3, LOB or row-overflow page, TEXT_MIXED.
■ PageType = 4, LOB or row-overflow page, TEXT_DATA.
■ PageType = 10, IAM page.
/****************************************************/

4 comments:

  1. Hi Saurabh,

    I have a similar scenario where some inserts/updates are failing due to maximum row size of 8060 limitation. I have a table with lots of varchar 250 to 500 columns, numerics, dates columns. I was trying to replicate that same scenario in test environment but unable to do so.

    Do you have any idea how to replicate it?

    Thanks,
    SQLPRODDBA

    ReplyDelete
  2. Restore you prod dab in test and make sure you are inserting rows of huge size. If you face issue you can share details with me.

    ReplyDelete