"Cannot create a row of size 8074 which is greater than the allowable maximum row size of 8060" while altering the table

I am trying to alter a column in a table. The existing table is like this:

CREATE TABLE [dbo].[table](
   [id1] [int] NOT NULL,
   [id2] [int] NOT NULL,
   [id3] [int] NOT NULL,
   [name] [nvarchar](255) NOT NULL,
   [id4] [int] NOT NULL,
   [xmlData] [xml](CONTENT [dbo].[xml_schema]) NULL,
   [booleanData1] [bit] NOT NULL,
   [notes] [varchar](4096) NULL,
   [id5] [int] NULL,
   [booleanData2] [bit] NULL,
   [id6] [int] NULL,

   CONSTRAINT [PK_table] PRIMARY KEY CLUSTERED 
   ([id1] ASC, [id2] ASC, [id3] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now I am trying to execute this sql on this table:

ALTER TABLE [table] ALTER COLUMN [xmlData] XML

so that I can drop the xml schema and replace it with new one.

But I am getting this error:


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

Can anyone tell me what is the problem here?

If you have previously removed or modified columns on this table, you may need to reclaim space before this operation will succeed. SQL Server does not always/usually immediately reclaim space for dropped or altered columns.

If the previous operations were all removals (or alterations) of variable-length columns, issuing DBCC CLEANTABLE ought to be sufficient. Otherwise, you will need to rebuild the table. You can do this by rebuilding the clustered index:

ALTER INDEX PK_table
ON dbo.table
REBUILD 
WITH (ONLINE = ON); -- Enterprise only, optional

StackExchange Database Administrators Q#69141, answer score: 27