Maximum Row Size Exceeded - Unsure How

• mlocatelli

  Grasshopper

  Points: 13

  More actions

  April 23, 2010 at 12:00 pm

  #218166

  Hi All,

  I'm having trouble wrapping my head around this one. I'm making some major structure changes to a table, mostly to dump data I don't need in it. Here was the original structure of the table:

  ID UniqueIdentifier (unique index on this)

  UserSessionID nvarchar(150) (primary key)

  UserSessionData nvarchar(max)

  OrganizationMatchCode nvarchar(50)

  CreatedDateTime datetime

  The new table structure I was going to use was this:

  UserSessionID nvarchar(150)

  OrganizationMatchCode nvarchar(50)

  CreatedDateTime datetime

  ID integer identity (1,1)

  I had written some scripts to modify this table in place that worked fine in my development environment (SQL 2005 Developer). When trying to run the same script in the production environment (SQL 2005 Standard), I ran into problems. I had dropped the unique constraint on ID and dropped the ID and UserSessionData columns. I used the following script (SSMS generated script) to add the ID identity column:

  BEGIN TRANSACTION

  SET QUOTED_IDENTIFIER ON

  SET ARITHABORT ON

  SET NUMERIC_ROUNDABORT OFF

  SET CONCAT_NULL_YIELDS_NULL ON

  SET ANSI_NULLS ON

  SET ANSI_PADDING ON

  SET ANSI_WARNINGS ON

  COMMIT

  BEGIN TRANSACTION

  GO

  ALTER TABLE dbo.t_cususersession ADD

  ID int NOT NULL IDENTITY (1, 1)

  GO

  COMMIT

  This ran for about 15-25 minutes, then it spit out this error:

  Msg 511, Level 16, State 1, Line 1

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

  The statement has been terminated.

  Msg 3902, Level 16, State 1, Line 1

  The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

  By my count, there was a maximum row size of 408 at this point and I was only adding another 4 bytes (I'm only counting data here, I don't know what other overhead there might be). In case the number of records matter, there were about 3.3M rows.

  I worked around this by creating a new table with the desired structure and migrating the data to the new table, but I am very curious as to why it's reporting this error. Any ideas?

  Thanks,

  Mike

• Jeff Moden

  SSC Guru

  Points: 1003888

  More actions

  April 23, 2010 at 6:27 pm

  #1157188

  mlocatelli (4/23/2010)

  ---

  Hi All,

  I'm having trouble wrapping my head around this one. I'm making some major structure changes to a table, mostly to dump data I don't need in it. Here was the original structure of the table:

  ID UniqueIdentifier (unique index on this)

  UserSessionID nvarchar(150) (primary key)

  UserSessionData nvarchar(max)

  OrganizationMatchCode nvarchar(50)

  CreatedDateTime datetime

  The new table structure I was going to use was this:

  UserSessionID nvarchar(150)

  OrganizationMatchCode nvarchar(50)

  CreatedDateTime datetime

  ID integer identity (1,1)

  I had written some scripts to modify this table in place that worked fine in my development environment (SQL 2005 Developer). When trying to run the same script in the production environment (SQL 2005 Standard), I ran into problems. I had dropped the unique constraint on ID and dropped the ID and UserSessionData columns. I used the following script (SSMS generated script) to add the ID identity column:

  BEGIN TRANSACTION

  SET QUOTED_IDENTIFIER ON

  SET ARITHABORT ON

  SET NUMERIC_ROUNDABORT OFF

  SET CONCAT_NULL_YIELDS_NULL ON

  SET ANSI_NULLS ON

  SET ANSI_PADDING ON

  SET ANSI_WARNINGS ON

  COMMIT

  BEGIN TRANSACTION

  GO

  ALTER TABLE dbo.t_cususersession ADD

  ID int NOT NULL IDENTITY (1, 1)

  GO

  COMMIT

  This ran for about 15-25 minutes, then it spit out this error:

  Msg 511, Level 16, State 1, Line 1

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

  The statement has been terminated.

  Msg 3902, Level 16, State 1, Line 1

  The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

  By my count, there was a maximum row size of 408 at this point and I was only adding another 4 bytes (I'm only counting data here, I don't know what other overhead there might be). In case the number of records matter, there were about 3.3M rows.

  I worked around this by creating a new table with the desired structure and migrating the data to the new table, but I am very curious as to why it's reporting this error. Any ideas?

  Thanks,

  Mike

  Don't try to do this all on your own. Just use SSMS to change the design... it'll write all the queries out that get executed in the background.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)