0

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
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.