How to Fix SQL/Server Page Size Too Small Errors

SQL/Server 2016 and later will sometimes complain about adding or altering table columns with a error like:

  • SQLServerException: Cannot create a row of size 8063 which is greater than the allowable maximum row size of 8060

If you do not understand what this means then google the message. To fix this issue keep reading.

Simple Solution – Rebuild the Problem Table

The simplest fix is to drop the table and let CopyStorm rebuild it automatically during the next backup. SQL/Server will examine the columns in the CREATE TABLE statement and avoid the problem.

A Harder Solution – Fix the Problem Table

The technique for fixing a table without rebuilding it involves converting the longest NVARCHAR(n) columnsĀ  to NVARCHAR(MAX). This will cause a SQL/Serve table to have a bit more row space for each column that is converted and thus allow the addition of new columns.

If you want to perform the fix by hand, here are the specific steps:

  • ALTER a few of the longer NVARCHAR2 columns to be NVARCHAR2(MAX)
  • DBCC CLEANTABLE (databaseName, ‘TableName’, 0 )
  • ALTER TABLE tableName REBUILD

If you would preferĀ  a computer to do the analysis for you and generate a script that will fix the problem then:

  • Paste the following script into SQL Server Management Studio
  • Change the values for @catalog, @schema, @column, and @cutoff to match the table your want to fix.
  • Run the script
  • Take the output of the script and paste into a new SQL Server Management Studio window and run it.

—
— Find all NVARCHAR columns longer than a specified cutoff and generate a script that changes them to NVARCHAR(MAX)
—
— This can be helpful if an alter to a table is causing the table to exceed the maximum page size.
—
— Note: those code generates a script that would make the column changes — it does not make the changes
—
BEGIN
DECLARE @catalog VARCHAR(128)
DECLARE @schema VARCHAR(128)
DECLARE @table VARCHAR(128)
DECLARE @column VARCHAR(128)
DECLARE @cutoff INTEGER
DECLARE @sql VARCHAR(MAX)

SELECT @catalog = ‘gsmithfarmer’ — look in this database
SELECT @schema = ‘dbo’ — look in this schema
SELECT @table = ‘account’ — change columns in this table.
SELECT @cutoff = 200 — ALTER NVARCHAR columns that are longer than this to NVARCHAR(MAX)

DECLARE @candidates TABLE (table_catalog VARCHAR(128), table_schema VARCHAR(128), table_name VARCHAR(128), column_name VARCHAR(128), character_maximum_length INTEGER );

INSERT INTO @candidates(table_catalog, table_schema, table_name, column_name, character_maximum_length )
SELECT table_catalog, table_schema, table_name, column_name, character_maximum_length
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_CATALOG=@catalog AND TABLE_SCHEMA=@schema AND TABLE_NAME=@table
AND DATA_TYPE=’nvarchar’ AND CHARACTER_MAXIMUM_LENGTH>@cutoff

;

DECLARE candCursor CURSOR FOR
SELECT table_catalog, table_schema, table_name, column_name
FROM @candidates ORDER BY character_maximum_length DESC
;

OPEN candCursor
FETCH NEXT FROM candCursor INTO @catalog, @schema, @table, @column
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql = ‘ALTER TABLE ‘ + @catalog + ‘.’ + @schema + ‘.’ + @table + ‘ ALTER COLUMN ‘ + @column + ‘ NVARCHAR(MAX) NULL;’

PRINT @sql
FETCH NEXT FROM candCursor INTO @catalog, @schema, @table, @column
END
CLOSE candCursor
DEALLOCATE candCursor
PRINT ‘DBCC CLEANTABLE (‘ + @catalog + ‘,’ + @table + ‘,0)’
PRINT ‘ALTER TABLE ‘ + @catalog + ‘.’ + @schema + ‘.’ + @table + ‘ REBUILD;’

END