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!
SQLStijn.com 