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

Question

I have already asked a question about this, but the problems keeps on hitting me ;-)

I have two tables that are identical. I want to add a xml column. In the first table this is no problem, but in the second table I get the sqlException (title). However, apart from the data in it, they are the same. So, can I get the sqlException because of data in the table?

I have also tried to store the field off page with

EXEC sp_tableoption 'dbo.PackageSessionNodesFinished', 
  'large value types out of row', 1

but without any succes. The same SqlException keeps coming.

First table: PackageSessionNodes

CREATE TABLE [dbo].[PackageSessionNodes](
    [PackageSessionNodeId] [int] IDENTITY(1,1) NOT NULL,
    [PackageSessionId] [int] NOT NULL,
    [TreeNodeId] [int] NOT NULL,
    [Duration] [int] NULL,
    [Score] [float] NOT NULL,
    [ScoreMax] [float] NOT NULL,
    [Interactions] [xml] NOT NULL,
    [BrainTeaser] [bit] NULL,
    [DateCreated] [datetime] NULL,
    [CompletionStatus] [int] NOT NULL,
    [ReducedScore] [float] NOT NULL,
    [ReducedScoreMax] [float] NOT NULL,
    [ContentInteractions] [xml] NOT NULL,
 CONSTRAINT [PK_PackageSessionNodes] PRIMARY KEY CLUSTERED 
(
    [PackageSessionNodeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Second table: PackageSessionNodesFinished

CREATE TABLE [dbo].[PackageSessionNodesFinished](
    [PackageSessionNodeFinishedId] [int] IDENTITY(1,1) NOT NULL,
    [PackageSessionId] [int] NOT NULL,
    [TreeNodeId] [int] NOT NULL,
    [Duration] [int] NULL,
    [Score] [float] NOT NULL,
    [ScoreMax] [float] NOT NULL,
    [Interactions] [xml] NOT NULL,
    [BrainTeaser] [bit] NULL,
    [DateCreated] [datetime] NULL,
    [CompletionStatus] [int] NOT NULL,
    [ReducedScore] [float] NOT NULL,
    [ReducedScoreMax] [float] NOT NULL,
    [ContentInteractions] [xml] NULL,
 CONSTRAINT [PK_PackageSessionNodesFinished] PRIMARY KEY CLUSTERED 
(
    [PackageSessionNodeFinishedId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

First script I tried to run (First two ALTER TABLE work fine, the third crashes on SqlException)

ALTER TABLE dbo.PackageSessionNodes ADD
    ContentInteractions xml NOT NULL CONSTRAINT 
    DF_PackageSessionNodes_ContentInteractions 
    DEFAULT (('<contentinteractions/>'));

ALTER TABLE dbo.PackageSessionNodes
    DROP CONSTRAINT DF_PackageSessionNodes_ContentInteractions

ALTER TABLE dbo.PackageSessionNodesFinished ADD
    ContentInteractions xml NOT NULL CONSTRAINT 
    DF_PackageSessionNodesFinished_ContentInteractions 
    DEFAULT (('<contentinteractions/>'));

ALTER TABLE dbo.PackageSessionNodesFinished
    DROP CONSTRAINT DF_PackageSessionNodesFinished_ContentInteractions

Second script I tried to run with the same result as previous script:

EXEC sp_tableoption 'dbo.PackageSessionNodes', 
    'large value types out of row', 1

ALTER TABLE dbo.PackageSessionNodes ADD
    ContentInteractions xml NOT NULL CONSTRAINT
    DF_PackageSessionNodes_ContentInteractions 
    DEFAULT (('<contentinteractions/>'));

ALTER TABLE dbo.PackageSessionNodes
    DROP CONSTRAINT DF_PackageSessionNodes_ContentInteractions

EXEC sp_tableoption 'dbo.PackageSessionNodesFinished', 
    'large value types out of row', 1

ALTER TABLE dbo.PackageSessionNodesFinished ADD
    ContentInteractions xml NOT NULL CONSTRAINT 
    DF_PackageSessionNodesFinished_ContentInteractions 
    DEFAULT (('<contentinteractions/>'));

ALTER TABLE dbo.PackageSessionNodesFinished
    DROP CONSTRAINT DF_PackageSessionNodesFinished_ContentInteractions

Now, In PackageSessionNodes there are 234 records, in PackageSessionNodesFinished there are 4256946 records.

Really would appreciate some help here as I'm stuck.

Answer 1

Try emptying your table first, make your change, then put the data back in again. It sounds like there is a row which is storing its existing data in the row, and adding the new column is just over its limit. If you take the data out, make the change, and then put it back again, it ought to store the xml out-of-row instead.

Unfortunately, it won't move data out of a row when you make this type if change. It's a very rare situation, you're unlucky.

Edit: also, you could try rebuilding your clustered index after you have set the table option, so that all the XML will be forced out of row. You could also do some maths and some calculating row lengths, to work out which row(s) are causing the problem. Then you could move that data temporarily.

Answer 2

It is down to the data. If you were to add this column to an empty table, or indeed to your first table, you would receive a warning that this may cause the maximum row size to exceed the limit, and that this could cause inserts or updates to fail. In the case of your second table, at least one row is already in the state where this is the case, and so it is the addition of the column that fails. Warnings are there for a reason and should rarely if ever be ignored.

So yes, you can get this because of data in the table.

Answer 3

FYI, running this SQL command on your DB can fix the problem if it is caused by space that needs to be reclaimed after dropping variable length columns:

DBCC CLEANTABLE (0,[dbo.TableName])

See: http://msdn.microsoft.com/en-us/library/ms174418.aspx

Answer 4

Besides running DBCC CLEANTABLE I also had to rebuild the table, using the commands below.

DBCC CLEANTABLE (TheNameOfYourDB, 'TableName', 0);
ALTER TABLE TableName REBUILD;