Database Administrators Asked by Gio on August 13, 2020
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP