TEMPDB: The Ghost of Version Store

Close to Halloween we noticed that some SQL Servers had started acting spooky, as if they wanted to add to the sentiment of Halloween. Last week I had one SQL Server doing exactly that. The server had been running perfectly for the past months but that day itsTempdDB was starting to fill up. Of course this required some investigation, and I found that VersionStore was the problem for my unexpected TempDB growth. The strange thing was that one database was taking up almost all of my tempdb space in version store, but it did not have any open transactions. Spooky stuff indeed!

So the ghost I was investigating was the fact that version store data of one database would not be removed from tempdb after the transaction was committed. To show you how the ghost worked and how you can solve it, I’ll continue with code examples of my haunting companion.

Since I am working on a Consolidated Server, I start off by creating two databases that have nothing to do with each other except for the fact that they are on the same server instance and share the resources.

CREATE DATABASE TESTDB_1
GO
CREATE DATABASE TESTDB_2
GO

Next, I’m going to create a table in each database

USE [TESTDB_1]
GO
CREATE TABLE TestTable (TTID int identity(-2147483648,1),c1 varchar(200))
GO
USE [TESTDB_2]
GO
CREATE TABLE TestTable (TTID int identity(-2147483648,1),c1 varchar(200))
GO

And insert some data into it

USE [TESTDB_1]
DECLARE @Insert int
SET @Insert = 1
WHILE @Insert <= 10000
BEGIN
INSERT INTO dbo.TestTable
VALUES (‘TEST’+cast(@Insert as varchar))
SET @Insert = @Insert + 1
END
GO
select top 10000 * from dbo.TestTable
GO
USE [TESTDB_2]
GO
DECLARE @Insert int
SET @Insert = 1
WHILE @Insert <= 10000
BEGIN
INSERT INTO dbo.TestTable
VALUES (‘TEST’+cast(@Insert as varchar))
SET @Insert = @Insert + 1
END
GO
select top 10000 * from dbo.TestTable
GO

Now I’m going to allow my databases to use snapshot so that we will be able to use the version store

ALTER DATABASE TESTDB_1 SET allow_snapshot_isolation ON
GO
ALTER DATABASE TESTDB_2 SET allow_snapshot_isolation ON
GO

I’m now going to execute an update on one of the tables in a committed transaction

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
USE TESTDB_1
update dbo.TestTable
SET c1 = ‘UpdatedValue’
COMMIT

If I go on and check the version store…

SELECT count(*) as amountofrec,d.name DBName from sys.dm_tran_version_store tvs
inner join sys.databases d on tvs.database_id = d.database_id
group by d.name

…we can see that there are some values in version store

So now we wait for the Tempdb garbage collector to kick in (this will take about a minute) and execute the query from above, so we can see that our version store is once again empty.

This is normal behavior. But today, I did not get the normal behavior. There was spooky behavior; this really small transaction stayed open for about two hours on one of my databases, plus a lot of update action on another database which I will demonstrate in the following code.

This is the open small transaction

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
USE TESTDB_1
update dbo.TestTable
SET c1 = ‘SmallTransactionUpdateNotCommitted’
where TTID = -2147483648

If we check the version store we see our one row

If we look for an OPEN transaction we can see that the transaction is still open on the database

Because it is an open transaction this row will not be deleted from our version store.

Now I will start mass updating on the other table in the other database. TESTDB_2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
USE TESTDB_2
update dbo.TestTable
SET c1 = ‘MultipleUpdates’
COMMIT
GO 10

As we can see this updated all the values ten times and committed the transaction

There are no open transactions left on the second database

As we check our version store, we see that the rows are now in the version store

If we wait a minute to let our garbage collector kick in, we can see that…

… the rows are still in version store!

So not just the row of the uncommitted transaction of one database will stay in version store, all the other rows of transactions that are committed after the first transaction was opened. This means that one open transaction can cause your tempdb to fill up with data from other databases!

To prove my point, I will now commit the open transaction and see if our version store gets cleared.

If we wait approximately one minute and check our version store again we can see that it has been cleared.

A spooky problem indeed! Even the smallest open transaction that uses version store in your consolidated database environment can haunt all of your databases.

The solution for this problem is quite straightforward: commit the open transaction or if you cannot commit it, kill it. One minute later, your version store will be empty again.

Thank you for reading, and stay tuned!