I have two availability group on the same SQL Server 2012 Instance (synchronous commit).
Trace flags 1118 and 1117 are both enabled.
TempDB has been growing for about two weeks: it starts from 36GB and now it's 130GB (4 data files).
I checked if there are any active transactions with
DBCC OPENTRAN command but nothing.
I run the following query:
SELECT db_name(spu.database_id) as database_name, ast.session_id, ast.transaction_id, ast.transaction_sequence_num, ast.elapsed_time_seconds, b.program_name, b.open_tran, b.status, ses.row_count, (spu.user_objects_alloc_page_count * 8) AS user_objects_kb, (spu.user_objects_dealloc_page_count * 8) AS user_objects_deallocated_kb, (spu.internal_objects_alloc_page_count * 8) AS internal_objects_kb, (spu.internal_objects_dealloc_page_count * 8) AS internal_objects_deallocated_kb, loginame, last_request_start_time, last_request_end_time, cmd, lastwaittype, dbid FROM sys.dm_tran_active_snapshot_database_transactions ast --JOIN sys.dm_tran_active_transactions at on at.transaction_id = ast.transaction_id JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id JOIN sys.sysprocesses b on ast.session_id = b.spid ORDER BY elapsed_time_seconds DESC
The output shows two rows with:
- transaction_id = 0
- cmd = "DB STARTUP"
- lastwaittype = REDO_THREAD_PENDING_WORK
and one of them has 1202046 elapsed_time_seconds: 13 days of elapsed time roughly corresponds to the beginning of version store problems.
Each row is related to a specific database with DBID 7.
I think, this active snapshot prevent version store cleanup but I can't kill them (transaction_id = 0) and I can't be sure of that: I know version store has 100 GB allocated (I used the following command) but I don't know who use it and who produce the snapshot:
SELECT SUM (user_object_reserved_page_count)*8/1024.0/1024.0 as user_obj_GB, SUM (internal_object_reserved_page_count)*8/1024.0/1024.0 as internal_obj_GB, SUM (version_store_reserved_page_count)*8/1024.0/1024.0 as version_store_GB, SUM (unallocated_extent_page_count)*8/1024.0/1024.0 as freespace_GB, SUM (mixed_extent_page_count)*8/1024.0/1024.0 as mixedextent_GB FROM sys.dm_db_file_space_usage