Blog Post

RCSI/SI doesn't work with rows larger than 8046 bytes


During this week I have prepared a workshop about Locking & Blocking for one of

my customers. One big part of this workshop was also the new Optimistic Concurrency

model that SQL Server offers us since the inception of SQL Server 2005. Since SQL

Server 2005 we have the READ COMMITTED SNAPSHOT ISOLATION level (RCSI) and the SNAPSHOT

ISOLATION level (SI). When you are using these new isolation levels readers (SELECT

statements) doesn’t acquire (S)hared Locks during their reading. Writers (UPDATE,

DELETE statements) are versioning the old image of the records they are changing into

the TempDb. They are creating a version chain where the actual version of the record

(which is stored on a data page inside the database) points to older versions that

are stored on pages in the TempDb. The following picture illustrates this concept.


To make this work, SQL Server has to add a 14 bytes long pointer to each record on

the data page inside the database. This means that each record gets 14 bytes longer.

As you might know, a record inside SQL Server can’t be longer than 8060 bytes when

you are using fixed length data types. This means that enabling RCSI/SI could lead

to records that are larger than these 8060 bytes. Let’s have a look on a very simple


USE master


-- Create a new database

CREATE DATABASE VersionStoreRestrictions


-- Enable RCSI



-- Use it

USE VersionStoreRestrictions


-- Create a table where each record is 8047 bytes large



   Column1 CHAR(40),

   Column2 CHAR(8000)



As you can see from the previous code I’m creating a table with two CHAR columns of

a length of 8040 bytes. SQL Server also needs internally at least 7 bytes overhead

for each record. In this case one record needs 8047 bytes on a data page. Because

we have enabled RCSI on the database level, SQL Server has to add the additional 14

bytes for the Row Version Pointer, which expands each record in the table to 8061

bytes. This means that each record is 1 byte too long for SQL Server. Let’s now insert

one record into the table:

-- Insert a initial row



When you now try to update the row (SQL Server now tries to version the old record

into TempDb), the connection to the database is broken by a 208 error:


SET Column1 = REPLICATE('B', 40)



Msg 208, Level 16, State 1, Line 2

Invalid object name 'TableB


The error message is not very meaningful, because the database context is wrong (SSMS

shows that you are now in the master database). But when you fully qualify the table

during the UPDATE statement, you are able to get back the actual error message:


UPDATE VersionStoreRestrictions.dbo.TableB

SET Column1 = REPLICATE('B', 40)



Msg 682, Level 22, State 214, Line 2

Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB

to check for any corruption.


Wow, that’s an internal error, because the buffer that is used by SQL Server is only

8060 bytes large, and now we try to store in that buffer 8061 byte – Voila that’s

a bug inside SQL Server! You can also reproduce this behavior on each version of SQL

Server starting with SQL Server 2005 which means that this bug is almost 6 years old!

Interesting enough the bug is already fixed in SQL Server Denali CTP1, where a page

dump shows that SQL Server stores the expected 8061 bytes. I’ve also already filed

a bug on Connect

so please feel free to vote for it!


When you are enabling RCSI/SI for existing databases, please keep this bug in mind,

because it means that RCSI/SI doesn’t work in any possible scenario. When you have

one table in your database that exceeds the 8046 bytes limit, then you are in real

troubles! With this nasty bug you can also see how important it is to know the internals

of SQL Server, and how SQL Server internally stores its data.




You rated this post out of 5. Change rating




You rated this post out of 5. Change rating