Sql-server – Does alter column change datatype use tempdb version store

alter-tablesql serversql server 2014tempdb

I was running alter column from INT to BIGINT, my table size is 250 GB and after 1 hour I got the version store error because tempdb was full.

As far as I know it only takes your memory pages and log files but I am not sure about Tempdb. Can someone please explain this internals?

I am using read committed isolation.

select 
    name, 
    snapshot_isolation_state_desc, 
    is_read_committed_snapshot_on 
from sys.databases

…for this database returns:

Name : DatabaseName 
Snapshot_isolation_state_desc : OFF
is_read_committed_snapshot_on : 0

Best Answer

Assuming we're talking about a plain ALTER TABLE, not an operation done through some wizard that does temp table and rename tricks or anything similar.

A straight column data type change may result in update of every row in the table. It depends on the types involved. You have to consider this: the row image in the table is just a bag of bytes, until is somehow interpreted by the engine. The engine follows the table metadata description to make sense of what those bytes are. The metadata will say 'from offset 12 to offset 16 is an int, the column number 4. From offset 17 to offset 19 is a short, the column number 5' and so on and so forth. Now, when you do an ALTER that changes a column type, the metadata will change. And now the $1M question: will the new metadata describe the same bag of bytes correctly? If yes, then the row ALTER is instantaneous and all the rows in the table remain unchanged. If not, then all rows have to be updated. A huge table will lead to a huge update, all in one transaction. This update can exhaust your resources (eg. run out of log space). Shanky is right that this 'update' may take the form of a temp table->insert, but that's an implementation detail.

I wrote some time ago about SQL Server table columns under the hood which shows how some of these operations occur, and how they leave evidence in the actual physical rowset metadata.

Now, you say that you are running out of version store space when this operation is done, even if snapshot was not enabled, right? The version store is used by more than just snapshot isolation. To give a well known example, the DELETED pseudo-table in triggers is powered from the version store. Which means that the version store may be used even if you did not enable snapshot isolation. See Managing TempDB in SQL Server: TempDB Basics (Version Store: Why do we need it?), Sunil quotes 3 non-snapshot related uses of the version-store: triggers, MARS and online index build.

SQL 2014 does not support online alter column, and as a DDL operation it should not require row-versioning for trigger nor for MARS. So IMHO it should not grow the version store.

My main suspect would be, in this case, a readable secondary (it's a shot in the dark here...). The fact that readable secondaries map all reads to snapshot isolation is well documented. I don't remember exactly the details and I may be wrong, but I think that in order for the secondary database to support snapshot isolation, the primary has to use the version store. Think about the fact that the secondary is a physical copy of the primary and the row size has to match.

Perhaps not an answer, but too long for a comment.

Related Question