Month: May 2021

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.

select * from sys.sysprocesses where spid = 123;

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:

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;