• Home
  • About
  • Blog
  • Speaking
  • Contact

TEMPDB: The Ghost of VersionStore

30 October 2015SQL StijnSQL2014No Comments

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!

Tags: DBA, SQL SERVER, T-SQL, TEMPDB, TRANSACTIONS, UNCOMMITTED TRANSACTIONS, VERSION STORE, VERSIONSTORE

Related Articles

Query Store: Here is the bacon!

14 March 2016SQL Stijn

The SQL Server Misconfiguration Chronicles: Part 1 Database AutoClose & AutoShrink

8 December 2015SQL Stijn

THE SQL SERVER MISCONFIGURATION CHRONICLES: PART 2 Instance Memory Settings

17 December 2015SQL Stijn

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Recent Posts

  • Building a Modern Lakehouse Data Warehouse with Azure Synapse Analytics: Moving your Database to the lake
  • Azure Synapse Dedicated SQL Pools: Workload Management
  • Synapse Spark – Playing with Data Frames with Python
  • Synapse Spark – Reading CSV files from Azure Data Lake Storage Gen 2 with Synapse Spark using Python
  • Azure Synapse Analytics – Serverless SQL Pools: Data Elimination

Recent Comments

    Archives

    • June 2022
    • December 2021
    • May 2021
    • April 2021
    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • August 2016
    • July 2016
    • March 2016
    • February 2016
    • December 2015
    • October 2015
    • August 2015
    • March 2015
    • December 2014
    • November 2014
    • July 2014
    • February 2014
    • January 2014
    • December 2013

    Categories

    • Azure Synapse Analytics
    • Building a Modern Lakehouse Data Warehouse
    • Geen categorie
    • MS PDW
    • SQL 2016
    • SQL Maintenance
    • SQL Server Migration
    • SQL Server Misconfiguration Chronicles
    • SQL2014
    • Troubleshooting

    Contact Stijn now


    © SQL Stijn 2017 - Privacy Policy - Cookie Policy
    Manage Cookie Consent
    To provide the best experiences, we use technologies like cookies to store and/or access device information.
    Functional Always active
    The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
    Preferences
    The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
    Statistics
    The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
    Marketing
    The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
    Manage options Manage services Manage vendors Read more about these purposes
    View preferences
    {title} {title} {title}