Version store – kto ho drží

/* DeadlockCafe | Version store – kto ho drží
   Účel:  Koľko miesta v tempdb zaberá version store (RCSI, snapshot, online index rebuild, triggre), ktorá databáza ho generuje a ktorá najstaršia snapshot transakcia bráni jeho čisteniu. Na 2019+ aj ADR persistent version store.
   Režim: READ ONLY | SQL Server 2016 – 2022 (sada 2 od 2016 SP2, sada 4 od 2019), on-premises
*/
SET NOCOUNT ON;

DECLARE @major int = CONVERT(int, SERVERPROPERTY('ProductMajorVersion'));
DECLARE @build int = CONVERT(int, PARSENAME(CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')), 2));

-- 1) version store v tempdb celkovo
SELECT CAST(SUM(version_store_reserved_page_count) * 8.0 / 1024 AS decimal(18,1)) AS version_store_mb,
       CAST(SUM(unallocated_extent_page_count)     * 8.0 / 1024 AS decimal(18,1)) AS tempdb_free_mb
FROM tempdb.sys.dm_db_file_space_usage;

-- 2) podľa databázy – sys.dm_tran_version_store_space_usage existuje od 2016 SP2 (13.0.5026)
IF @major > 13 OR (@major = 13 AND @build >= 5026)
    EXEC (N'SELECT DB_NAME(database_id) AS database_name,
                   CAST(reserved_space_kb / 1024.0 AS decimal(18,1)) AS version_store_mb,
                   reserved_page_count
            FROM sys.dm_tran_version_store_space_usage
            WHERE reserved_page_count > 0
            ORDER BY reserved_space_kb DESC;');
ELSE
    PRINT N'sys.dm_tran_version_store_space_usage vyžaduje SQL Server 2016 SP2 alebo novší – sada 2 preskočená.';

-- 3) najstaršie snapshot transakcie (držia verzie)
SELECT TOP (20)
       a.session_id,
       a.transaction_id,
       a.elapsed_time_seconds,
       a.is_snapshot,
       a.max_version_chain_traversed,
       a.average_version_chain_traversed,
       s.status                 AS session_status,
       s.login_name, s.host_name, s.program_name,
       s.open_transaction_count,
       DB_NAME(s.database_id)   AS session_database,
       r.command, r.wait_type,
       ib.event_info            AS last_submitted_command
FROM sys.dm_tran_active_snapshot_database_transactions AS a
LEFT JOIN sys.dm_exec_sessions AS s ON s.session_id = a.session_id
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = a.session_id
OUTER APPLY sys.dm_exec_input_buffer(a.session_id, NULL) AS ib
ORDER BY a.elapsed_time_seconds DESC;

-- 4) ADR persistent version store (SQL Server 2019+)
IF @major >= 15
    EXEC (N'SELECT DB_NAME(p.database_id) AS database_name,
                   CAST(p.persistent_version_store_size_kb / 1024.0 AS decimal(18,1))   AS pvs_mb,
                   CAST(p.online_index_version_store_size_kb / 1024.0 AS decimal(18,1)) AS online_index_vs_mb,
                   p.current_aborted_transaction_count,
                   p.oldest_active_transaction_id,
                   at.transaction_begin_time AS oldest_active_tran_begin,
                   st.session_id             AS oldest_active_tran_session,
                   p.oldest_aborted_transaction_id,
                   p.offrow_version_cleaner_start_time,
                   p.offrow_version_cleaner_end_time
            FROM sys.dm_tran_persistent_version_store_stats AS p
            JOIN sys.databases AS d ON d.database_id = p.database_id
            LEFT JOIN sys.dm_tran_active_transactions AS at ON at.transaction_id = p.oldest_active_transaction_id
            LEFT JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = p.oldest_active_transaction_id
            WHERE d.is_accelerated_database_recovery_on = 1
            ORDER BY p.persistent_version_store_size_kb DESC;');