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:

    db_name(spu.database_id) as database_name,
    (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,
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:

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 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir