Tempdb issues

-- Queries to check Tempdb full issues.


--User_Objects
select convert(numeric(10,2),round(sum(data_pages)*8/1024.,2)) as user_object_reserved_MB from tempdb.sys.allocation_units a
inner join tempdb.sys.partitions b on a.container_id = b.partition_id
inner join tempdb.sys.objects c on b.object_id = c.object_id

sp_who2

select * from sys.sysprocesses

--Version store
select getdate() AS runtime, SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8  as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage

--Long running Query
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;


--Space used by internal Objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

dbcc opentran()

SELECT [name],[log_reuse_wait_desc] FROM sys.databases
WHERE [name]='tempdb'

--query to identify the sessions that are utilizing most of TempDB
SELECT session_id,(user_objects_alloc_page_count*8/1024) AS SpaceUsedByTheSessionMB,*
FROM sys.dm_db_session_space_usage DDSSU
WHERE database_id=DB_ID('tempdb')
AND user_objects_alloc_page_count > 0