SQL Server 2012 – 8k Row Overflow Error When Updating Row of Size 5k

sql serversql-server-2012update

I'm trying to update a target table which has one row of size 5k to a row of size 5k also.

Since it's one row it's easy to know the row actual size:

select *
from sys.dm_db_index_physical_stats(DB_ID('RODS_HSD_ES'), 
OBJECT_ID(N'TBL_BM_HSD_SUBJECT_AN_148_REPRO'), NULL, NULL, 'DETAILED')

Reproduce

Table wasn't altered since creation. don't see any reason why it should fail. Ideas?

Best Answer

The problem is related to the fact you are updating the clustering key, and the destination table happens to have a partitioning scheme¹. When SQL Server is requested to update any component of the clustering key, it must perform an UPDATE and DELETE, or a hybrid update where some of the rows are updated in-place, and some are not.

If you remove the clustered index from the destination table, you'll see the update works.

The error message, while perhaps a bit misleading, is accurate since the resulting row size during the update exceeds the maximum length.

I suggest you consider changing the structure of the table to:

not use VARCHAR(MAX) for all those columns. If you don't actually need 2GB of characters in a single column, why define the column that way? Define the column to be the maximum size that will be realistically encountered.
perhaps split this table into several tables where the resulting maximum row size is less than 8060 bytes. It seems you have several logical clusters of columns, such as the V_MAX_xxx, V_64_xxx, and V_512_xxx columns, etc.

To simplify your repro, you may want to eliminate the cursor, and only do the following DML operation:

UPDATE dbo.TBL_BM_HSD_SUBJECT_AN_148_REPRO_TARGET
SET [sampletime]  = '2015-12-29 01:11:26.687';

The above column is one of the components of the clustering key and also the partitioning key (updating other CI key columns works fine).

With the clustered index in place, you get this error:

Msg 511, Level 16, State 1, Line 1

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

The statement has been terminated.

Without the clustered index in place, the statement succeeds.

¹ Interestingly, if we eliminate the partitioning from the repro, we find the update succeeds, even with the clustered index in place.

Related Solutions

Sql-server – Referencing database programmatically via T-SQL

Dynamic SQL comes in handy for these types of administrative tasks. Here is a snippet from a stored procedure I wrote that not only gets the defragmentation levels, but also generates the code to do the defragmentation:

select @SQL = 
'
select getdate(),
       ''' + @@ServerName + ''',
       ''' + @DatabaseName + ''',
       so.Name,
       si.Name,
       db_id(''' + @DatabaseName + '''),
       ips.object_id,
       ips.index_id,
       ips.index_type_desc,
       ips.alloc_unit_type_desc,
       ips.index_depth,
       ips.avg_fragmentation_in_percent,
       ips.fragment_count,
       avg_fragment_size_in_pages,
       ips.page_count,
       ips.record_count,
       case
         when ips.index_id = 0 then ''alter table [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
         else ''alter index '' + si.name + '' on [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
       end
  from sys.dm_db_index_physical_stats(db_id(''' + @DatabaseName + '''),null,null,null, ''' + @SampleMode + ''') ips
  join [' + @DatabaseName + '].sys.objects so  on so.object_id = ips.object_id
  join [' + @DatabaseName + '].sys.schemas ss  on ss.schema_id = so.schema_id
  join [' + @DatabaseName + '].sys.indexes si  on si.object_id = ips.object_id
                      and si.index_id  = ips.index_id
order by so.Name, ips.index_id
'

exec (@SQL)

Sql-server – Why does OBJECT_ID return NULL in a computed column

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not yourdatabase.sys.objects, since the table is created in tempdb. Try adding the database prefix to the insert, and that will make OBJECT_ID go find it in the right place:

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');

SELECT name, [object_id] FROM #procs;

Results:

dbo.sp_send_dbmail          NULL
msdb.dbo.sp_send_dbmail     283148054

Best Answer

Related Solutions

Sql-server – Referencing database programmatically via T-SQL

Sql-server – Why does OBJECT_ID return NULL in a computed column

Related Question