Cannot rebuild index because row above maximum size (Microsoft SQL)

Question

I'm trying to rebuild a clustered index on a SQL Server 2005 table, and I keep getting the error:

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

The command is just a standard ALTER INDEX <name> ON <table> REBUILD.

I have no idea how I've managed to get 8078 bytes into a single row - from my understanding of SQL, that shouldn't be possible.

If I do a query to check how much data there is in the rows (ie Datalength(col1)+datalength(col2) ...) then the largest row in the table appears to be 6389 bytes, which is fine in relation to the 8060 limit.

I've tried copying the table (using Import Export Wizard) to another database or another server and I get the same errors about an 8078-byte row.

score 3 · Answer 1 · answered Apr 26 '12 at 12:14

3

Does this post help?

The suggestion is to create a new TEXT column (which is not subject to the row length limit), copy your variable-length column into it dropping the old column and running DBCC CLEANTABLE. Do this for all variable length columns then change them back to how they were.

answered Apr 26 '12 at 12:14

dwurf

920
8
15

Actually, in this case, there aren't lots of variable length columns, but lots of nullable columns. Same difference, though. I'll have a look. – Richard Gadsden Apr 26 '12 at 12:52
Looks like there are lots of fixed length nullable columns. Certain rows have lots of empty columns (ie '' instead of NULL) and that appears to be the problem. I'm going to have to do a manual insert and replace all the ''s with NULLS – Richard Gadsden Apr 26 '12 at 15:04

score 1 · Accepted Answer · answered Apr 26 '12 at 15:44

It seems that some program was putting blanks into text fields instead of leaving them as nulls, which it was supposed to do..

The resolution was to copy the data to a new table (using INSERT rather than SSIS) but with a condition on every text field in the SELECT like this:

CASE datalength([col1]) WHEN 0 THEN NULL ELSE [col1] END AS [col1]

This dropped the empty text fields which solved the issue - obviously, the data then has to get copied back in again, but I can just do TRUNCATE TABLE [tablename] and then INSERT INTO [tablename] SELECT * FROM [copyoftable]

I think it may be time to explain about the existence of varchar(MAX) to some people.

Cannot rebuild index because row above maximum size (Microsoft SQL)

2 Answers2