AnswerBun.com

TempDB Version Store used by DB STARTUP background process

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:

  • 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

One Answer

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

Add your own answers!

Related Questions

Automatically purging binary logs

6  Asked on January 6, 2022 by kriegu

 

Query customers attributes

1  Asked on January 6, 2022 by mac-portter

         

MongoDB different members of cluster choosing different index

1  Asked on January 4, 2022 by marc-esher

   

The opposite of ordering by a column in SQL

2  Asked on January 2, 2022 by john-hamelink

       

How to deal with a person that may have multiple alias names?

2  Asked on January 2, 2022 by javascriptloser

   

Service Broker locks

1  Asked on January 2, 2022

   

How can I find a view column’s base table column?

3  Asked on December 31, 2021 by durette

       

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir