CODEWITHSUNDEEP
sqlsql-server
Scarabas
Scarabas

Reputation: 103

Why does my row size exceed the allowed maximum of 8060 bytes

I have the following table in SQL Server 2012, Web Edition:

CREATE TABLE [dbo].[MyTable] 
(
     [Id]               INT            IDENTITY (1, 1) NOT NULL,
     [Created]          DATETIME       DEFAULT (getdate()) NOT NULL,
     [RefId]            INT            NULL,
     [Name]             NVARCHAR (128) NULL,
     [Email]            NVARCHAR (128) NULL,
     [ImageUrl]         NVARCHAR (256) NULL,
     [Url]              VARCHAR (256)  NULL,
     [Age]              TINYINT        NULL,
     [Country]          VARCHAR (6)    NULL,
     [Location]         NVARCHAR (192) NULL,
     [People]           INT            NULL,
     [Categories]       NVARCHAR (128) NULL,
     [Block]            BIT            DEFAULT ((0)) NOT NULL,
     [GeneratedRevenue] INT            NULL,
     [IsFemale]         BIT            DEFAULT ((1)) NULL,
     [HasInstalled]     BIT            NULL,
     [Keywords]         VARCHAR (128)  NULL,
     [Brands]           NVARCHAR (512) NULL,
     [Source]           TINYINT        NULL,
     [Alias]            VARCHAR (65)   NULL,

     PRIMARY KEY CLUSTERED ([Id] ASC)
);

As far as I gather, the total size should be 3175 bytes; but I regularly get the following error, when updating the table:

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

How does the above result in a row size of 8068?

Edit: I should mention that this table has been altered, uses Change Tracking and has four indexes.

Also, if I copy the contents to a new table with the same definition, no errors occur for a while, but do come back.

Upvotes: 1

Views: 2880

Answers (1)

The_Torst
The_Torst

Reputation: 401

You say you use Change Tracking. Are you - by any chance - ignoring the versioning part of Change Tracking, and resetting the entries by doing the following?

ALTER TABLE dbo.MyTable disable change_tracking
ALTER TABLE dbo.MyTable enable change_tracking

If so, you may have a suspect. Change Tracking adds an 8 bit column behind the scenes every time you reenable Change Tracking, which is dropped if it already exists. Since dropping a column is just a meta operation, you may have a large number of dropped 8 bit columns lurking behind the scenes, depending on the frequency with which you reenable Change Tracking.

To check this, look at the system_internals_partition_columns view and see if you have a large number of is_dropped colums. There could be more reasons for having many of those, but this way of using Change Tracking is one of them.

I see Remus Rusanu is linking to a good article in a comment (rusanu.com/2011/10/20/sql-server-table-columns-under-the-hoo‌​d): the queries he lists should be what you need to see if the above is the case.

Edit: In case you need to delete the dropped columns, you can rebuild the clustered index for the table(s) that have many dropped columns. This means that rebuilding the clustered index for MyTable will relieve you of your symptom.

Upvotes: 5

Related Questions