SQL Server 2012 – 8Kb Row Size Limits Ignored Sometimes

sql-server-2012varbinary

As a result of poorly tested code (not mine!) I've ended up with a table containing 319 VARBINARY(MAX) fields, plus two DATETIME fields and two UNIQUEIDENTIFIER fields. Obviously this is not ideal, but it should still be within the limits of what SQL Server can handle in terms of row size.

As I understand, VARBINARY(MAX) is stored on-table as a 24-byte pointer to off-row storage. 24*319 = 7656 bytes, plus the other four fields = 7704 bytes, so I'm just in the limit of what I can put into one row.

This all behaved fine until the code started inserting data into this table. It got 6 rows in before crashing with the message:

Cannot create a row of size 8345 which is greater than the allowable maximum row size of 8060. The statement has been terminated.

This is very puzzling as none of the rows should be any larger than the others (on-row at least). I've scripted the table off and it looks fine. I did notice that it was created with ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] although I'm not sure what effect that would have.

Does anyone know what might be happening here, and more importantly how do I get around it?

Here's the code used to create the database, and the insert command

CREATE DATABASE MyBigDB;
GO
USE [MyBigDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Autogenerated table with really big data](
    [Primary_key] [uniqueidentifier] NULL,
    [A Foreign key] [uniqueidentifier] NULL,
    [Created] [datetime] NULL,
    [Updated] [datetime] NULL,
    [c1] [varbinary](max) NULL,
    [c2] [varbinary](max) NULL,
    [c3] [varbinary](max) NULL,
    [c4] [varbinary](max) NULL,
    ...
    ...
    etc
    ...
    ...
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

INSERT INTO [Autogenerated table with really big data] ([Primary_key], [A Foreign key], [Created], [Updated], [c1], [c2], [c3], [c4],............)
VALUES (<a UID>, <a UID>, <a DATETIME>, <a DATETIME>, <binary blob>, <binary blob>,............)

Best Answer

SQL Version:

Microsoft SQL Server 2012 - 11.0.5343.0 (X64)

Response:

The pointer to the LOB_DATA page (your 24 byte pointer) is only used when the row containing your VARBINARY(MAX) column (s) no longer can fit on the page.

I am still working on matching the exact scenario you mentioned above.

Reference(s):

Slot Array and Total Page Size

USE [master];
GO

IF DATABASEPROPERTYEX (N'RowSize', N'Version') > 0
BEGIN
    ALTER DATABASE [RowSize] SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [RowSize];
END;
GO

CREATE DATABASE [RowSize];
GO

USE [RowSize];
GO

IF OBJECT_ID('test' , 'U') IS NOT NULL
BEGIN
     DROP TABLE dbo.test;
END;

-- Create the wide table
CREATE TABLE dbo.test
(
      c1 UNIQUEIDENTIFIER NULL
    , c2 UNIQUEIDENTIFIER NULL
    , c3 DATETIME NULL
    , c4 DATETIME NULL
    , c5 VARBINARY(MAX) NULL
    , c6 VARBINARY(MAX) NULL
    , c7 VARBINARY(MAX) NULL
    , c8 VARBINARY(MAX) NULL
    , c9 VARBINARY(MAX) NULL
    , c10 VARBINARY(MAX) NULL
    , c11 VARBINARY(MAX) NULL
    , c12 VARBINARY(MAX) NULL
    , c13 VARBINARY(MAX) NULL
    , c14 VARBINARY(MAX) NULL
    , c15 VARBINARY(MAX) NULL
    , c16 VARBINARY(MAX) NULL
    , c17 VARBINARY(MAX) NULL
    , c18 VARBINARY(MAX) NULL
    , c19 VARBINARY(MAX) NULL
    , c20 VARBINARY(MAX) NULL
    , c21 VARBINARY(MAX) NULL
    , c22 VARBINARY(MAX) NULL
    , c23 VARBINARY(MAX) NULL
    , c24 VARBINARY(MAX) NULL
    , c25 VARBINARY(MAX) NULL
    , c26 VARBINARY(MAX) NULL
    , c27 VARBINARY(MAX) NULL
    , c28 VARBINARY(MAX) NULL
    , c29 VARBINARY(MAX) NULL
    , c30 VARBINARY(MAX) NULL
    , c31 VARBINARY(MAX) NULL
    , c32 VARBINARY(MAX) NULL
    , c33 VARBINARY(MAX) NULL
    , c34 VARBINARY(MAX) NULL
    , c35 VARBINARY(MAX) NULL
    , c36 VARBINARY(MAX) NULL
    , c37 VARBINARY(MAX) NULL
    , c38 VARBINARY(MAX) NULL
    , c39 VARBINARY(MAX) NULL
    , c40 VARBINARY(MAX) NULL
    , c41 VARBINARY(MAX) NULL
    , c42 VARBINARY(MAX) NULL
    , c43 VARBINARY(MAX) NULL
    , c44 VARBINARY(MAX) NULL
    , c45 VARBINARY(MAX) NULL
    , c46 VARBINARY(MAX) NULL
    , c47 VARBINARY(MAX) NULL
    , c48 VARBINARY(MAX) NULL
    , c49 VARBINARY(MAX) NULL
    , c50 VARBINARY(MAX) NULL
    , c51 VARBINARY(MAX) NULL
    , c52 VARBINARY(MAX) NULL
    , c53 VARBINARY(MAX) NULL
    , c54 VARBINARY(MAX) NULL
    , c55 VARBINARY(MAX) NULL
    , c56 VARBINARY(MAX) NULL
    , c57 VARBINARY(MAX) NULL
    , c58 VARBINARY(MAX) NULL
    , c59 VARBINARY(MAX) NULL
    , c60 VARBINARY(MAX) NULL
    , c61 VARBINARY(MAX) NULL
    , c62 VARBINARY(MAX) NULL
    , c63 VARBINARY(MAX) NULL
    , c64 VARBINARY(MAX) NULL
    , c65 VARBINARY(MAX) NULL
    , c66 VARBINARY(MAX) NULL
    , c67 VARBINARY(MAX) NULL
    , c68 VARBINARY(MAX) NULL
    , c69 VARBINARY(MAX) NULL
    , c70 VARBINARY(MAX) NULL
    , c71 VARBINARY(MAX) NULL
    , c72 VARBINARY(MAX) NULL
    , c73 VARBINARY(MAX) NULL
    , c74 VARBINARY(MAX) NULL
    , c75 VARBINARY(MAX) NULL
    , c76 VARBINARY(MAX) NULL
    , c77 VARBINARY(MAX) NULL
    , c78 VARBINARY(MAX) NULL
    , c79 VARBINARY(MAX) NULL
    , c80 VARBINARY(MAX) NULL
    , c81 VARBINARY(MAX) NULL
    , c82 VARBINARY(MAX) NULL
    , c83 VARBINARY(MAX) NULL
    , c84 VARBINARY(MAX) NULL
    , c85 VARBINARY(MAX) NULL
    , c86 VARBINARY(MAX) NULL
    , c87 VARBINARY(MAX) NULL
    , c88 VARBINARY(MAX) NULL
    , c89 VARBINARY(MAX) NULL
    , c90 VARBINARY(MAX) NULL
    , c91 VARBINARY(MAX) NULL
    , c92 VARBINARY(MAX) NULL
    , c93 VARBINARY(MAX) NULL
    , c94 VARBINARY(MAX) NULL
    , c95 VARBINARY(MAX) NULL
    , c96 VARBINARY(MAX) NULL
    , c97 VARBINARY(MAX) NULL
    , c98 VARBINARY(MAX) NULL
    , c99 VARBINARY(MAX) NULL
    , c100 VARBINARY(MAX) NULL
    , c101 VARBINARY(MAX) NULL
    , c102 VARBINARY(MAX) NULL
    , c103 VARBINARY(MAX) NULL
    , c104 VARBINARY(MAX) NULL
    , c105 VARBINARY(MAX) NULL
    , c106 VARBINARY(MAX) NULL
    , c107 VARBINARY(MAX) NULL
    , c108 VARBINARY(MAX) NULL
    , c109 VARBINARY(MAX) NULL
    , c110 VARBINARY(MAX) NULL
    , c111 VARBINARY(MAX) NULL
    , c112 VARBINARY(MAX) NULL
    , c113 VARBINARY(MAX) NULL
    , c114 VARBINARY(MAX) NULL
    , c115 VARBINARY(MAX) NULL
    , c116 VARBINARY(MAX) NULL
    , c117 VARBINARY(MAX) NULL
    , c118 VARBINARY(MAX) NULL
    , c119 VARBINARY(MAX) NULL
    , c120 VARBINARY(MAX) NULL
    , c121 VARBINARY(MAX) NULL
    , c122 VARBINARY(MAX) NULL
    , c123 VARBINARY(MAX) NULL
    , c124 VARBINARY(MAX) NULL
    , c125 VARBINARY(MAX) NULL
    , c126 VARBINARY(MAX) NULL
    , c127 VARBINARY(MAX) NULL
    , c128 VARBINARY(MAX) NULL
    , c129 VARBINARY(MAX) NULL
    , c130 VARBINARY(MAX) NULL
    , c131 VARBINARY(MAX) NULL
    , c132 VARBINARY(MAX) NULL
    , c133 VARBINARY(MAX) NULL
    , c134 VARBINARY(MAX) NULL
    , c135 VARBINARY(MAX) NULL
    , c136 VARBINARY(MAX) NULL
    , c137 VARBINARY(MAX) NULL
    , c138 VARBINARY(MAX) NULL
    , c139 VARBINARY(MAX) NULL
    , c140 VARBINARY(MAX) NULL
    , c141 VARBINARY(MAX) NULL
    , c142 VARBINARY(MAX) NULL
    , c143 VARBINARY(MAX) NULL
    , c144 VARBINARY(MAX) NULL
    , c145 VARBINARY(MAX) NULL
    , c146 VARBINARY(MAX) NULL
    , c147 VARBINARY(MAX) NULL
    , c148 VARBINARY(MAX) NULL
    , c149 VARBINARY(MAX) NULL
    , c150 VARBINARY(MAX) NULL
    , c151 VARBINARY(MAX) NULL
    , c152 VARBINARY(MAX) NULL
    , c153 VARBINARY(MAX) NULL
    , c154 VARBINARY(MAX) NULL
    , c155 VARBINARY(MAX) NULL
    , c156 VARBINARY(MAX) NULL
    , c157 VARBINARY(MAX) NULL
    , c158 VARBINARY(MAX) NULL
    , c159 VARBINARY(MAX) NULL
    , c160 VARBINARY(MAX) NULL
    , c161 VARBINARY(MAX) NULL
    , c162 VARBINARY(MAX) NULL
    , c163 VARBINARY(MAX) NULL
    , c164 VARBINARY(MAX) NULL
    , c165 VARBINARY(MAX) NULL
    , c166 VARBINARY(MAX) NULL
    , c167 VARBINARY(MAX) NULL
    , c168 VARBINARY(MAX) NULL
    , c169 VARBINARY(MAX) NULL
    , c170 VARBINARY(MAX) NULL
    , c171 VARBINARY(MAX) NULL
    , c172 VARBINARY(MAX) NULL
    , c173 VARBINARY(MAX) NULL
    , c174 VARBINARY(MAX) NULL
    , c175 VARBINARY(MAX) NULL
    , c176 VARBINARY(MAX) NULL
    , c177 VARBINARY(MAX) NULL
    , c178 VARBINARY(MAX) NULL
    , c179 VARBINARY(MAX) NULL
    , c180 VARBINARY(MAX) NULL
    , c181 VARBINARY(MAX) NULL
    , c182 VARBINARY(MAX) NULL
    , c183 VARBINARY(MAX) NULL
    , c184 VARBINARY(MAX) NULL
    , c185 VARBINARY(MAX) NULL
    , c186 VARBINARY(MAX) NULL
    , c187 VARBINARY(MAX) NULL
    , c188 VARBINARY(MAX) NULL
    , c189 VARBINARY(MAX) NULL
    , c190 VARBINARY(MAX) NULL
    , c191 VARBINARY(MAX) NULL
    , c192 VARBINARY(MAX) NULL
    , c193 VARBINARY(MAX) NULL
    , c194 VARBINARY(MAX) NULL
    , c195 VARBINARY(MAX) NULL
    , c196 VARBINARY(MAX) NULL
    , c197 VARBINARY(MAX) NULL
    , c198 VARBINARY(MAX) NULL
    , c199 VARBINARY(MAX) NULL
    , c200 VARBINARY(MAX) NULL
    , c201 VARBINARY(MAX) NULL
    , c202 VARBINARY(MAX) NULL
    , c203 VARBINARY(MAX) NULL
    , c204 VARBINARY(MAX) NULL
    , c205 VARBINARY(MAX) NULL
    , c206 VARBINARY(MAX) NULL
    , c207 VARBINARY(MAX) NULL
    , c208 VARBINARY(MAX) NULL
    , c209 VARBINARY(MAX) NULL
    , c210 VARBINARY(MAX) NULL
    , c211 VARBINARY(MAX) NULL
    , c212 VARBINARY(MAX) NULL
    , c213 VARBINARY(MAX) NULL
    , c214 VARBINARY(MAX) NULL
    , c215 VARBINARY(MAX) NULL
    , c216 VARBINARY(MAX) NULL
    , c217 VARBINARY(MAX) NULL
    , c218 VARBINARY(MAX) NULL
    , c219 VARBINARY(MAX) NULL
    , c220 VARBINARY(MAX) NULL
    , c221 VARBINARY(MAX) NULL
    , c222 VARBINARY(MAX) NULL
    , c223 VARBINARY(MAX) NULL
    , c224 VARBINARY(MAX) NULL
    , c225 VARBINARY(MAX) NULL
    , c226 VARBINARY(MAX) NULL
    , c227 VARBINARY(MAX) NULL
    , c228 VARBINARY(MAX) NULL
    , c229 VARBINARY(MAX) NULL
    , c230 VARBINARY(MAX) NULL
    , c231 VARBINARY(MAX) NULL
    , c232 VARBINARY(MAX) NULL
    , c233 VARBINARY(MAX) NULL
    , c234 VARBINARY(MAX) NULL
    , c235 VARBINARY(MAX) NULL
    , c236 VARBINARY(MAX) NULL
    , c237 VARBINARY(MAX) NULL
    , c238 VARBINARY(MAX) NULL
    , c239 VARBINARY(MAX) NULL
    , c240 VARBINARY(MAX) NULL
    , c241 VARBINARY(MAX) NULL
    , c242 VARBINARY(MAX) NULL
    , c243 VARBINARY(MAX) NULL
    , c244 VARBINARY(MAX) NULL
    , c245 VARBINARY(MAX) NULL
    , c246 VARBINARY(MAX) NULL
    , c247 VARBINARY(MAX) NULL
    , c248 VARBINARY(MAX) NULL
    , c249 VARBINARY(MAX) NULL
    , c250 VARBINARY(MAX) NULL
    , c251 VARBINARY(MAX) NULL
    , c252 VARBINARY(MAX) NULL
    , c253 VARBINARY(MAX) NULL
    , c254 VARBINARY(MAX) NULL
    , c255 VARBINARY(MAX) NULL
    , c256 VARBINARY(MAX) NULL
    , c257 VARBINARY(MAX) NULL
    , c258 VARBINARY(MAX) NULL
    , c259 VARBINARY(MAX) NULL
    , c260 VARBINARY(MAX) NULL
    , c261 VARBINARY(MAX) NULL
    , c262 VARBINARY(MAX) NULL
    , c263 VARBINARY(MAX) NULL
    , c264 VARBINARY(MAX) NULL
    , c265 VARBINARY(MAX) NULL
    , c266 VARBINARY(MAX) NULL
    , c267 VARBINARY(MAX) NULL
    , c268 VARBINARY(MAX) NULL
    , c269 VARBINARY(MAX) NULL
    , c270 VARBINARY(MAX) NULL
    , c271 VARBINARY(MAX) NULL
    , c272 VARBINARY(MAX) NULL
    , c273 VARBINARY(MAX) NULL
    , c274 VARBINARY(MAX) NULL
    , c275 VARBINARY(MAX) NULL
    , c276 VARBINARY(MAX) NULL
    , c277 VARBINARY(MAX) NULL
    , c278 VARBINARY(MAX) NULL
    , c279 VARBINARY(MAX) NULL
    , c280 VARBINARY(MAX) NULL
    , c281 VARBINARY(MAX) NULL
    , c282 VARBINARY(MAX) NULL
    , c283 VARBINARY(MAX) NULL
    , c284 VARBINARY(MAX) NULL
    , c285 VARBINARY(MAX) NULL
    , c286 VARBINARY(MAX) NULL
    , c287 VARBINARY(MAX) NULL
    , c288 VARBINARY(MAX) NULL
    , c289 VARBINARY(MAX) NULL
    , c290 VARBINARY(MAX) NULL
    , c291 VARBINARY(MAX) NULL
    , c292 VARBINARY(MAX) NULL
    , c293 VARBINARY(MAX) NULL
    , c294 VARBINARY(MAX) NULL
    , c295 VARBINARY(MAX) NULL
    , c296 VARBINARY(MAX) NULL
    , c297 VARBINARY(MAX) NULL
    , c298 VARBINARY(MAX) NULL
    , c299 VARBINARY(MAX) NULL
    , c300 VARBINARY(MAX) NULL
    , c301 VARBINARY(MAX) NULL
    , c302 VARBINARY(MAX) NULL
    , c303 VARBINARY(MAX) NULL
    , c304 VARBINARY(MAX) NULL
    , c305 VARBINARY(MAX) NULL
    , c306 VARBINARY(MAX) NULL
    , c307 VARBINARY(MAX) NULL
    , c308 VARBINARY(MAX) NULL
    , c309 VARBINARY(MAX) NULL
    , c310 VARBINARY(MAX) NULL
    , c311 VARBINARY(MAX) NULL
    , c312 VARBINARY(MAX) NULL
    , c313 VARBINARY(MAX) NULL
    , c314 VARBINARY(MAX) NULL
    , c315 VARBINARY(MAX) NULL
    , c316 VARBINARY(MAX) NULL
    , c317 VARBINARY(MAX) NULL
    , c318 VARBINARY(MAX) NULL
    , c319 VARBINARY(MAX) NULL
    , c320 VARBINARY(MAX) NULL
    , c321 VARBINARY(MAX) NULL
    , c322 VARBINARY(MAX) NULL
    , c323 VARBINARY(MAX) NULL
);
GO
/* I GET THIS WARNING...

Warning: The table "test" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. 
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

*/

-- Create one row
INSERT INTO dbo.test (c1 , c2 , c3 , c4)
    VALUES (NEWID() , NEWID() , GETDATE() , GETDATE());

-- Is it there?
SELECT * FROM dbo.test;
GO


-- How many pages?
DBCC IND (RowSize,test,-1); -- We have the IAM page and a single data page
GO                          -- Note that the row is stored as IN_ROW_DATA at this point.
DBCC TRACEON(3604);
GO
DBCC PAGE (RowSize, 1, 283, 3);
GO

/*
m_freeCnt = 7999
10003400 c203754c 9b36f44b 8dafee3b b32bd7c6
3106ac3a 8ec6854e b68291e6 4ccf7872 b321e100
f2a40000 b321e100 f2a40000 4301f0ff ffffffff
ffffffff ffffffff ffffffff ffffffff ffffffff
ffffffff ffffffff ffffffff ffff07

Tag A:              0x10
Tag B:              0x00
Null Bitmap Offset: 0x0034 = 52 dec.
GUID 1:             c203754c 9b36f44b 8dafee3b b32bd7c6
GUID 2:             3106ac3a 8ec6854e b68291e6 4ccf7872 
DATETIME 1:         b321e100 f2a40000
DATETIME 2:         b321e100 f2a40000
Column Count:       0x0143 = 323 dec.
NULL Bitmap:        0xc0fff...

Variable Length Column Count
+ Variable Length Column Offset Array 
+ Variable Length Columns: Do not exist at the moment    
   
Total Overhead: 
    Tag A:              1B
    Tag B:              1B
    Null Bitmap Offset: 2B
    Column Count:       2B
    NULL Bitmap:        41B
    Page Header:        96B
    Slot Array:         2B  = 145B 

Fixed Size of Row: 
    (16 + 16 + 8 + 8)B = 48B

Total Used Bytes: 145B + 48B = 193B = 8192B [8K page] - 7999B [m_freeCnt]

*****************************
Note: There are no pointers stored. The row is stored as IN_ROW_DATA at this point.
*****************************
*/

-- Update one row
UPDATE dbo.test
SET  c5  = CONVERT(VARBINARY(MAX), REPLICATE(0x11, 7961));
GO

-- What is m_freeCnt now?
--      m_freeCnt = 34

-- How many pages?
DBCC IND (RowSize,test,-1); -- We have the IAM page and a single data page
GO
DBCC PAGE (RowSize, 1, 283, 3);
GO

/*

m_freeCnt can be made to equal 20 when snapshot isolation is turned on and used,
but it cannot go lower than that, since the remaining bytes are 
reserved for future use by the SQL Server team


Variable Length Column Count:         0x0001 (2B)
Variable Length Column Offset Array:  0x157c (2B) 
Variable Length Columns:              0x11111111...


*/

-- Update one row
UPDATE dbo.test
SET  c5  = CONVERT(VARBINARY(MAX), REPLICATE(0x11, 7962));
GO

-- How many pages?
DBCC IND (RowSize,test,-1); -- We have 2 IAM pages, one IN_ROW_DATA page, and one LOB_DATA page
GO
DBCC PAGE (RowSize, 1, 283, 3);
GO

/*

Your 24 byte pointer is on page 283 ( the IN_ROW_DATA page)

*/
Related Question