Version store bloat detected

In MSSQL database log, there are following errors :

Date 1/05/2021 1:02:03 PM
Log SQL Server (Current -  1/05/2021 1:02:03  PM)

Source spid 123

Message
Version store bloat detected
Date  1/05/2021 1:02:03  PM 
Log SQL Server (Current -  1/05/2021 1:02:03  PM )

Source spid 123

Message
Error: 51000, Severity: 17, State: 1.

Check which session caused this issue:

select * from sys.sysprocesses where spid = 123;

Check tempdb space usage:

use tempdb
exec sp_spaceused @oneresultset = 1

Check all version records in the version store in each database:

select db_name(database_id), COUNT(*)
from sys.dm_tran_version_store
GROUP BY database_id

dbname	        totalVersionRecords
TESTDB  	5535970
msdb	        126687
...
..
.

Check space usage of objects in tempdb:

select  
 db_name(database_id) as dbname, 
 FILEGROUP_ID, 
 FILE_ID,    
 version_store_reserved_page_count/128/1024 AS Version_Store_Size_GB 
from 
  tempdb.sys.dm_db_file_space_usage ;

dbname	FILEGROUP_ID	FILE_ID	Version_Store_Size_GB
tempdb	1	        1	5
tempdb	1	        3	2
tempdb	1	        4	5
tempdb	1	        5	2
tempdb	1	        6	2
tempdb	1	        7	2
tempdb	1	        8	2
tempdb	1	        9	2


SELECT * FROM sysfiles order by groupid;

 Check all active transactions that generate or potentially access row versions:

is_snapshotint0 = Is not a snapshot isolation transaction.

1 = Is a snapshot isolation transaction.
select session_id, is_snapshot, transaction_sequence_num, commit_sequence_num, elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions
order by 5 desc;

Microsoft SQL Server Versions List

 

Microsoft SQL Server Versions List

https://sqlserverbuilds.blogspot.com/?_sm_au_=iHVkRTWZkQ346ff32sfHjK0s0FFFG#sql2017x

Please show your generosity to help us maintain and improve this website.
One-Time
Monthly
Yearly

Make a one-time donation

Make a monthly donation

Make a yearly donation

Choose an amount

$5.00
$15.00
$100.00
$5.00
$15.00
$100.00
$5.00
$15.00
$100.00

Or enter a custom amount

$

Your contribution is appreciated.

Your contribution is appreciated.

Your contribution is appreciated.

DonateDonate monthlyDonate yearly