TEMPDB: The Ghost of VersionStore

Close to the 30th of October we find that some SQL Servers start acting spooky, as if they want to add to the sentiment of Halloween. This week I had one SQL Server doing exactly that. The server was running without any problems for the past months but today his TempdDB was starting to fill up. Of course this required some investigation, and I found that Version Store 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 today 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 ghost.

Since I am working on a consolidated server I start off with creating two databases which have nothing to do with each other except 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 versionstore

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 1 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 now 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 see that there are some values in version store

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

This is the normal behavior. But today, I was not having the normal behavior, I was having spooky behaviour, I had this really small transaction which stayed open for about 2 hours on one of my databases, and 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

 

When we now check the versionstore 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 versionstore.

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 10 times and committed the transaction

There are no open transactions left on the second database

If we go check our version store we will 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 only the row of the uncommitted transaction of 1 database will stay in version store, also all the other rows of transaction who 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 about a minute and go check our versionstore again we can see that it has been cleared!

 

A spooky problem indeed! Because this means that even the smallest open transaction which 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. Then after a minute your version store will be empty again.

 

Thank you for reading, and stay tuned!

Advertisements
Tagged , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: