CODEWITHSUNDEEP
sql-server-2014
TacoV
TacoV

Reputation: 474

Maximum row size exceeds the allowed maximum of 8060 bytes

In run Microsoft SQL Server 2014. Upon altering a table, I get a warning about the row size.

ALTER TABLE myTable
    ALTER COLUMN aRandomColumn NVARCHAR(10);

Warning: The table "myTable" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

The table has 75 columns:

By my calculations, the nvarchars take up 1120 bytes, the other columns 121 (not sure but it won't be much more).

How does this exceed 8060 bytes? How can I get rid of this warning?

Tried to sum all columns with isnull(datalength(myColumnName), 1) and the actual data never exceeds 600.

Found a similar question but change tracking is off for me, so it didn't help. Also cleantable, found in this question, didn't help.

However, when I copy the table, the new table does not produce this warning.

SELECT * INTO myNewTable FROM myTable;
-- (8561 row(s) affected)
ALTER TABLE myNewTable
    ATLER COLUMN aRandomColumn NVARCHAR(10);
-- Command(s) completed successfully.

Upvotes: 7

Views: 20160

Answers (2)

Ali Kamali
Ali Kamali

Reputation: 153

Initially, deleting some columns from the table might not immediately resolve the problem. The reason is that deleting columns does not automatically reclaim the space they occupied in each row. Therefore, even after column deletion, the row size may still exceed the maximum allowed size.

To resolve the issue, you need to rebuild the table using the ALTER TABLE statement with the REBUILD option. This operation reorganizes the table's data pages and reclaims the space previously occupied by the deleted columns.

ALTER TABLE [Table] REBUILD;

Upvotes: 3

TacoV
TacoV

Reputation: 474

Found the solution for my problem.

This question deals with the same issue, and points to the solution in another location.

This is likely due to previous alters (particularly of existing column widths) that are still reflected in the underlying page structure. Try rebuilding the table or dropping/re-creating the clustered index to reclaim that space.

What worked for me was:

ALTER TABLE myTable
    ADD CONSTRAINT pk_bigchange PRIMARY KEY (aColumnWithUniqueNonNullValues);
GO
ALTER TABLE myTable
    DROP CONSTRAINT pk_bigchange;
GO

Upvotes: 4

Related Questions