# 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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://learn.capstorm.com/frequently-asked-questions/problems-solutions/how-to-fix-sql-server-page-size-too-small-errors.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.