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:
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
I checked if in other enviroments the same query on dm_tran_active_snapshot_database_transactions returns the same results and no! It isn't normal having DB STARTUP snapshot active from days.
I checked the errorlog of 13 days ago and I found errors about an unexpected shutdown and failover.
So I tried to remove the database with DBID 7 from the availability group and I re-run the two queries: Now tempdb and version store are empty and the sessions with transaction_id 0 are gone.
I re-added the database to his availability group and I resolved the issue.
Answered by Gio on August 13, 2020
1 Asked on January 6, 2022
1 Asked on January 6, 2022 by franz-huebner
1 Asked on January 6, 2022 by mac-portter
1 Asked on January 4, 2022 by marc-esher
1 Asked on January 4, 2022 by lares-dk
2 Asked on January 4, 2022
2 Asked on January 4, 2022 by crashmeister
2 Asked on January 2, 2022 by john-hamelink
1 Asked on January 2, 2022 by james-randall
1 Asked on January 2, 2022
0 Asked on January 2, 2022 by gandalf
1 Asked on January 2, 2022 by atmdev
3 Asked on December 31, 2021 by durette
1 Asked on December 31, 2021 by triffids
1 Asked on December 31, 2021 by j-brune
4 Asked on December 31, 2021
Get help from others!