7

I have a scenario where some inserts/updates are failing due to maximum row size of 8060 limitation. This is happening for a table having lots of varchar 250 to 500, numerics and datetime columns. I was trying to reproduce this same scenario in my test environment but unable to do so.

Do you guys have any idea how to reproduce it in Test environment? Any examples would be great for explanation.

My environment is SQL server 2005 Ent. Edition. My table has ~470 columns. I know its a bad design but its not in my control.

I tried below but it doesn't work:

create table test (col1 varchar(5000), col2 varchar(5000))   
insert into test values (replicate('A', 5000), replicate('B', 5000))

Even though record length is greater than 8060, it inserts successfully.

2
  • In your attempt to recreate, did you have non-null values in the fixed length (numeric and datetime) columns as well as the max length in the varchar columns. I would expect that would recreate the problem.
    – Dan Guzman
    Jul 3, 2015 at 16:36
  • Please script out the CREATE TABLE and add it to your question. Jul 5, 2015 at 15:57

2 Answers 2

5

SQL Server has a limitation of 8060 bytes per row. See "Bytes per row" in Maximum Capacity Specifications for SQL Server. Additional information for this line has this phrase:

SQL Server supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row; because of this, the effective row limit is higher than in previous releases of SQL Server. For more information, see the "Row-Overflow Data Exceeding 8 KB" topic in SQL Server Books Online.

So, when you insert two varchar values each 5000 bytes long in one row, one or both of them would be stored off-row and occupy only 24 bytes instead of 5000+2.

But, if you have 470 varchar columns in a table, you can insert only 8060/24=335 non-null varchar values in the same row of such table. In fact, even slightly less than 335, because there are other structures in a row that take some space.

MSDN: Row-Overflow Data Exceeding 8 KB

So, to reproduce it in test environment, create a table with at least 336 varchar columns and try to insert 336 non-null values that are longer than 24 characters in the same row.

3

This is due to the fact you reached the row size limitation. But this should affect your production server too?

Are you sure that both server are the same and both server use the same table design?

You can try to use nvarchar(max) instead of (e.g). nvarchar(250) for testing purposes. This will use more space and will be slower, but will avoid the 8060 limit. It should be ok for testing purposes.

8
  • My question was how to replicate the same error in test environment.
    – SQLPRODDBA
    Jul 3, 2015 at 16:11
  • Ah well. Quite easy. Just put enough data in a row. If you have 20 Rows which can hold varchar(500), you can fill each of them with a string which is 400 signs long (8000 bytes in length). If you try to fill one them with 500 instead of 400 (8100 bytes in length), it will fail due to your error message.
    – Ionic
    Jul 3, 2015 at 18:05
  • No it doesn't work. Both rows got inserted successfully. I created a table with 20 columns having varchar(500). 1st test: Inserted all 20 columns with 400 signs long data. It got inserted successfully. 2nd test: Inserted 19 columns with 400 signs long data and one with 500 signs long data. That too got inserted successfully.
    – SQLPRODDBA
    Jul 4, 2015 at 7:21
  • Both servers use the same version and configuration?
    – Ionic
    Jul 4, 2015 at 7:46
  • Yes. Both servers use same version and configuration.
    – SQLPRODDBA
    Jul 6, 2015 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.