What happens if the Version Stored gets filled completely

· Uncategorized

What happens if your version store in TEMPDB fills up? Opposite to transactionlog behavior (Where the process that fills the tranlog is rolled back), the versionstore doesn’t penalize the process responsible for generating the versions, but rather the poor select statement that needs to read from the version store. How unfair…

The script hereunder demonstrates just that.

Have fun

SET NOCOUNT ON
GO
-- IMPORTANT
-- Limit size of TEMPDB datafile to 10MB disable autogrowth    --

——————————
— Create a db to play with —
——————————
USE [master]
GO
CREATE DATABASE testVersionStore ON  PRIMARY
( NAME = N’testVersionStore’, FILENAME = N’…\testVersionStore.mdf’ , SIZE = 50MB , FILEGROWTH = 0)
LOG ON
( NAME = N’testVersionStore_log’, FILENAME = N’…\testVersionStore_log.LDF’ , SIZE = 100MB , FILEGROWTH = 0)
GO
ALTER DATABASE testVersionStore set read_committed_snapshot ON
go
ALTER DATABASE testVersionStore set allow_snapshot_isolation ON
go
ALTER DATABASE [testVersionStore] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE [testVersionStore]
GO
———————————
— Create a table to test with —
———————————
CREATE TABLE dbo.TableVS (c1 bigint identity, JustALargeCol NCHAR(100) default ‘A’);
GO
CREATE CLUSTERED INDEX c_ix ON dbo.TableVS (c1);
GO

— Insert 100000 recs: takes +/- 45 sec
INSERT dbo.TableVS DEFAULT VALUES;
GO 100000

— Check VS–> no records because inserts don’t generate versions
select COUNT(*) from sys.dm_tran_version_store

— Update 500 recs and check the VS
SET ROWCOUNT 500
UPDATE dbo.TableVS SET JustALargeCol = ‘abc’;
SET ROWCOUNT 0
select COUNT(*) from sys.dm_tran_version_store;

— Wait 1 min, check VS –> empty (garbage collector)
select COUNT(*) from sys.dm_tran_version_store

— Update another 500 recs in an explicit tran
BEGIN TRAN
SET ROWCOUNT 500
UPDATE dbo.TableVS SET JustALargeCol = ‘xyz’;

— Open a new session and run following
use [testVersionStore]
go
select * from dbo.TableVS

— COMMIT Tran: Running the select query again will hold the changes
SET ROWCOUNT 0
COMMIT

— delete table in an explicit tran so VS will run out of space
exec sp_cycle_errorlog
BEGIN TRAN
DELETE  dbo.TableVS;

— rerun the select statement in the second session while the delete statement is not committed, an error should occur because the VS is not accessible
— Msg 3958, Level 16, State 1, Line 1
— Transaction aborted when accessing versioned row in table ‘dbo.TableVS’ in database ‘testVersionStore’. Requested versioned row was not found. Your tempdb is probably out of space. Please refer to BOL on how to configure tempdb for versioning.
— also valid if the DELETE statement is replaced by an UPDATE statement: UPDATE dbo.TableVS SET JustALargeCol = ‘xyz’;
commit
use [testVersionStore]
go
select * from dbo.TableVS

— after DELETE/UPDATE statement is finished
—    * run the select statement in the second session: should run correctly returning 0 or updated records
—    * run select COUNT(*) from sys.dm_tran_version_store –> empty
—    * Check errorlog for errors:  xp_readerrorlog –> error message

/*
Conclusion: when VS fills up, select statements that need to access the VS will fail, but the DELETE/UPDATE statement will succeed
*/
——————————
— Cleanup —
——————————
USE master
GO
DROP DATABASE testVersionStore
GO

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: