AzureTempDB_SpaceMonitoring.txt

-- (1) get Database name
select db_name() as DatabaseName

-- (2) Get SLO Level
select * 
from sys.database_service_objectives

-- (3) Get TempDB information current and max size
SELECT 
	FILE_ID,
	type_desc,
	SizeInMB = format(size*1.0/128,'#,###0'),
	MaxSizeinMB = format(max_size*1.0/128,'#,##0')
FROM tempdb.sys.database_files 


-- (4) From the current size, how much is used or free 
SELECT 
[free space in MB]			= format((SUM(unallocated_extent_page_count)*1.0/128),'#,##0'), 
[used space in MB]			= format((SUM(allocated_extent_page_count)*1.0/128),'#,##0'),
[VersionStore space in MB]	= format((SUM(version_store_reserved_page_count)*1.0/128),'#,##0') 
FROM tempdb.sys.dm_db_file_space_usage; 

-- (5) Get Allocations by session.
;with TempDBAlloc
as
(
SELECT  
		((user_objects_alloc_page_count-user_objects_dealloc_page_count) + (internal_objects_alloc_page_count-internal_objects_dealloc_page_count)) / 129 AS tempdb_current_usage_MB,
		es.session_id, 
		es.host_name,
		es.program_name,
		es.login_name,
		es.last_request_end_time
FROM sys.dm_db_session_space_usage ss
	join sys.dm_exec_sessions es on es.session_id = ss.session_id
)
select * 
from TempDBAlloc
where tempdb_current_usage_MB>0
order by tempdb_current_usage_MB desc


-- (6) get size for temp tables
SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    tempdb.sys.tables(nolock) t
INNER JOIN 
    tempdb.sys.schemas(nolock) s ON s.schema_id = t.schema_id
INNER JOIN      
    tempdb.sys.indexes(nolock) i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    tempdb.sys.partitions(nolock) p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    tempdb.sys.allocation_units(nolock) a ON p.partition_id = a.container_id
WHERE 
    t.NAME LIKE '#%'    -- filter out system tables for diagramming
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    s.Name, t.Name

-- (7) Get Log file information
select counter_name,instance_name, SizeInMB=cntr_value/1024
from sys.dm_os_performance_counters 
where counter_name in('Log File(s) Size (KB)','Log File(s) Used Size (KB)')
and instance_name='tempdb'

--(8) Drill down to get information about log consumers
;with CTE as
(select 
	DBT.database_id,
	SS.session_id,
	SS.host_name,
	SS.program_name,
	SS.login_name,
	DBT.transaction_id, 
	DBT.database_transaction_begin_time, 
	database_transaction_type_desc = case DBT.database_transaction_type
		when 1 then 'Read/write transaction'
		when 2 then 'Read-only transaction'
		when 3 then 'System transaction'
	end,
	database_transaction_state_desc = case DBT.database_transaction_state
		when 1  then 'The transaction has not been initialized'
		when 3  then 'The transaction has been initialized but has not generated any log records'
		when 4  then 'The transaction has generated log records'
		when 5  then 'The transaction has been prepared'
		when 10 then 'The transaction has been committed'
		when 11 then 'The transaction has been rolled back'
		when 12 then 'The transaction is being committed. (The log record is being generated, but has not been materialized or persisted.'
	end,
	SST.open_transaction_count,
	TotalLogSpaceReserved = format(1.0*(DBT.database_transaction_log_bytes_reserved+DBT.database_transaction_log_bytes_reserved_system)/1024/1024,'#,##0'),
	database_transaction_log_bytes_used_MB = format(1.0*DBT.database_transaction_log_bytes_used/1024/1024,'#,##0'),
	database_transaction_log_bytes_reserved_MB = format(1.0*DBT.database_transaction_log_bytes_reserved/1024/1024,'#,##0'),
	database_transaction_log_bytes_used_system_MB = format(1.0*DBT.database_transaction_log_bytes_used_system/1024/1024,'#,##0'),
	database_transaction_log_bytes_reserved_system_MB = format(1.0*DBT.database_transaction_log_bytes_reserved_system/1024/1024,'#,##0')
From tempdb.sys.dm_tran_database_transactions DBT
	 left join tempdb.sys.dm_tran_session_transactions SST on DBT.transaction_id = SST.transaction_id
	 left join tempdb.sys.dm_exec_sessions SS on SS.session_id = SST.session_id
)
select
	database_id, 
	session_id, 
	host_name, 
	program_name, 
	transaction_id,
	database_transaction_begin_time,
	database_transaction_type_desc,
	database_transaction_state_desc,
	TotalLogSpaceReserved,
	database_transaction_log_bytes_used_MB,
	database_transaction_log_bytes_reserved_MB ,
	database_transaction_log_bytes_used_system_MB,
	database_transaction_log_bytes_reserved_system_MB	
	from CTE 
	where transaction_id in (select transaction_id from CTE where TotalLogSpaceReserved>0)
-- in case of using elastic pool you may switch to master and query for the database name of the database ID returned by this query


-- another option to monitor TemoDB usage is by using Adam Machanic WhoIsActive stored procedure.
-- http://whoisactive.com/