SQL Server tempDB usage...

--Below script is for check tempDB usage

--------------------------------------------------------------------------------

use tempdb

go

select * from Sys.dm_db_file_space_usage

--Is the version store taking up a lot of space?

SELECT version_store_Gb = (SUM(version_store_reserved_page_count)*8/1024./1024.) 

, total_tempdb_Gb = sum(total_page_count)*8/1024./1024.

, pct_of_tempdb_dedicated_to_version_store = round((sum(version_store_reserved_page_count)*1./sum(total_page_count)*1.)*100.,2)

FROM sys.dm_db_file_space_usage;

--If version store is very large, need to find out what transactions are holding onto it.

--It is not possible to associate versionstore allocation to individual sessions

--But these queries would be responsible for version store-leveraging transactions

SELECT              Observed = SYSDATETIMEOFFSET(),

tst.session_id,

tat.transaction_id,

s.login_name,

s.host_name,

s.program_name,

tat.[name],

                    tat.transaction_begin_time ,

                    elapsed_min = DATEDIFF(mi, tat.transaction_begin_time, sysdatetime()),

                    transaction_type_desc = CASE tat.transaction_type

   WHEN 1 THEN 'Read/write'

WHEN 2 THEN 'Read-only'

WHEN 3 THEN 'System'

WHEN 4 THEN 'Distributed'

END ,

                   transaction_description = CASE tat.transaction_state

                                         WHEN 0 THEN 'The transaction has not been completely initialized yet.'

                                         WHEN 1 THEN 'The transaction has been initialized but has not started.'

                                         WHEN 2 THEN 'The transaction is active.'

                                         WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'

                                         WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'

                                         WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'

                                         WHEN 6 THEN 'The transaction has been committed.'

                                         WHEN 7 THEN 'The transaction is being rolled back.'

                                         WHEN 8 THEN 'The transaction has been rolled back.'

                    END

, non_versionstore_total_tempdb_allocatd_Gb = ( sum (ssu.internal_objects_alloc_page_count) - sum (ssu.internal_objects_dealloc_page_count)

+ sum (ssu.user_objects_alloc_page_count) - sum (ssu.user_objects_dealloc_page_count)

+ sum (tsu.internal_objects_alloc_page_count) - sum (tsu.internal_objects_dealloc_page_count)

+ sum (tsu.user_objects_alloc_page_count) - sum (tsu.user_objects_dealloc_page_count)

)*8/1024./1024.

FROM tempdb.sys.dm_tran_active_snapshot_database_transactions sdt

inner join sys.dm_tran_active_transactions tat on tat.transaction_id = sdt.transaction_id

left outer join sys.dm_tran_session_transactions tst on tat.transaction_id = tst.transaction_id

left outer join sys.dm_exec_sessions s on s.session_id = tst.session_id

left outer join tempdb.sys.dm_db_session_space_usage ssu      on ssu.session_id = tst.session_id

inner join tempdb.sys.dm_db_task_space_usage tsu      on ssu.session_id = tsu.session_id

group by tst.session_id, tat.transaction_id,

s.login_name,

s.host_name,

s.program_name,

tat.[name], tat.transaction_begin_time, tat.transaction_type, tat.transaction_state

order by transaction_begin_time asc

/*

--Which database has the most data stored in tempdb's version store?

--WARNING this query can have a performance impact!

select database_id, sum(aggregated_record_length_in_bytes)

from sys.dm_tran_top_version_generators WITH (NOLOCK)

group by database_id

*/