Home / server / Questions / 383381
Accepted
Richard Gadsden
Asked: 2012-04-27 03:39:36 +0800 CST

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

  • 772

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.

sql-server-2005

2 Answers

  1. 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.

    • 3
  2. Best Answer

    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.

    • 1