HELP ! - SQL Server 8060 record length length limit reached !

[Martin11]

Our replicate instance is stuck trying to add a column to our target (SQL server 2019) that was added to the source.

This statement keeps timing out and retrying:

ADD COLUMN NewColumnName varchar(20)

SQL trace indicates that adding this column causes max row size among existing rows to exceed limit of 8,060 bytes

And latency and incoming changes are growing

Thanks,

Martin

[SwathiPulagam · Accepted solution]

Hi @Martin11 ,

SQL server cannot allocate more than 8060 bytes. Please verify your target table structure and see how many bytes are already allocated for that table.

Below is the link for the article:

https://www.sqlskills.com/blogs/paul/the-curious-case-of-the-8060-byte-row-size-limit/

Thanks,

Swathi

[Martin11]

At this point we have 32,000,000 changes on disk waiting to be applied - I presume not all are for this one table that needs the column added.
If we stop the task and change this table to only replicate a subset of the columns in the source table, can we resume the task then, pick up what's relevant (assuming there are some) to the other tables in our target and then reload this one table with fewer columns ?

[Dana_Baldwin]

Hi @Martin11 

Yes that should work. As you mention, the modified table will need to be reloaded.

Thanks,

Dana

[Martin11]

Update:

As a first try to fix the issue,  we rebuilt the clustered index for the table (in ONLINE mode) after web research suggested that it might fix the issue - it did not.

We then decided to try and just reload the table.

We needed to add some filters to this table for other reasons so we stopped the task,  added the filters to the table - which we knew would trigger a reload upon task resumption.

We then resumed the task and the table began reloading and completed without issue !

Also, all of the pending 32,000,000 changes on disk for target application finally went down to zero (took about 2 hours) !

------------------------------------

Recap Observations:

Before we stopped the task,  we made backup copy of the entire database onto the same server.  The DBA then ran the command (ADD COLUMN NEWCOLUMNNAME varchar(20)) that was timing out (per Attunity log message) and it ran !  It took about 30 minutes but the table has about 17,000,000 rows.

I think every timeout on the SQL server that we can find we've set to 0 (no timeout) to help with other issues surrounding stored proc timeouts, etc that we've had over the years.  

Would the timeout that Attunity reported on this command be a SQL timeout or an Attunity timeout ?  And if SQL timeout, would anybody know where would it be set in SQL  ?

Thanks,

Martin

[Dana_Baldwin]

Hi @Martin11 

Timeout errors happen when Replicate issues a query on the source (sometimes the target) and does not get a response before the configured timeout value.

The following timeout parameters can be set on the SQL Server Endpoint > Advanced Settings:

cdcTimeout, default 600 seconds
>> helps with timeout issues when Replicate is reading the transaction log. Sets the timeout for CDC queries such as fn_dump or fn_dblog timeout.

executeTimeout, default 60 seconds
>> Sets the timeout when waiting for a query executed in the DB (truncate, insert from select etc.)

loadTimeout, default 1200 seconds

>>This timeout is more related to the select * query used during full load

A general rule of thumb is to triple the default values and increase from there as needed. Keep in mind that the performance of the database & network play a role as well, and should be examined (e.g. index & statistics maintenance).

These same internal parameters are available on SQL Server target endpoints. Timeout parameters and their default values vary by endpoint.

I hope this helps!

Dana