8Kb row size limits ignored - but only sometimes!

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 (, , , , , ,............)

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

StackExchange Database Administrators Q#110813, answer score: 6