T-SQL Monitor TempDB Usage:
Use TEMPDB go
SELECT SUM(size)/128 AS [Total TempDB Database Size (MB)] FROM tempdb.sys.database_files
SELECT UPPER(instance_name) AS 'Database', [Data File(s) Size (KB)]/1024 AS [Data File Total Size (MB)], [Log File(s) Size (KB)]/1024 AS [Log File Total Size (MB)], [Log File(s) Used Size (KB)]/1024 AS [Log File Space Used (MB)] FROM (SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN ('Data File(s) Size (KB)', 'Log File(s) Size (KB)', 'Log File(s) Used Size (KB)') AND instance_name = 'tempdb') AS A PIVOT (MAX(cntr_value) FOR counter_name IN ([Data File(s) Size (KB)], [LOG File(s) Size (KB)], [Log File(s) Used Size (KB)])) AS B
SELECT (SUM(unallocated_extent_page_count)/128) AS [TempDB Free Space (MB)], SUM(internal_object_reserved_page_count)*8 AS [TempDB Internal Objects (KB)], SUM(user_object_reserved_page_count)*8 AS [TempDB User Objects (KB)], SUM(version_store_reserved_page_count)*8 AS [TempDB Version Store (KB)] FROM sys.dm_db_file_space_usage WHERE database_id = 2
Select CASE Type_Desc when 'ROWS' Then 'DATA' WHEN 'LOG' THEN 'LOG' End as 'TempDB File Type', count(*) as '# Files' from tempdb.sys.database_files group by Type_Desc order by Type_Desc DESC
SELECT tb.name AS [Temporary Table Name], stt.row_count AS [Number of rows], stt.used_page_count * 8 AS [Used space (KB)], stt.reserved_page_count * 8 AS [Reserved space (KB)] FROM tempdb.sys.partitions AS prt INNER JOIN tempdb.sys.dm_db_partition_stats AS stt ON prt.partition_id = stt.partition_id AND prt.partition_number = stt.partition_number INNER JOIN tempdb.sys.tables AS tb ON stt.object_id = tb.object_id where stt.row_count > 0 ORDER BY stt.row_count desc
SELECT session_id, user_objects_alloc_page_count/128 AS user_objs_total_sizeMB, (user_objects_alloc_page_count - user_objects_dealloc_page_count)/128.0 AS user_objs_active_sizeMB, internal_objects_alloc_page_count/128 AS internal_objs_total_sizeMB, (internal_objects_alloc_page_count - internal_objects_dealloc_page_count)/128.0 AS internal_objs_active_sizeMB FROM sys.dm_db_session_space_usage ORDER BY user_objects_alloc_page_count DESC
SELECT SERVERPROPERTY('MachineName') AS MachineName, ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLSERVER') AS InstanceName, SERVERPROPERTY('ServerName') AS SQLInstance, DB_NAME(r.database_id) AS [Database], s.login_name AS LoginName, t.session_id AS SPID, s.[status] AS [Status], s.[program_name] AS ProgramName, r.command AS StatementCommand, SUBSTRING( est.[text], (r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN-1 THEN DATALENGTH(est.[text]) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 ) + 1 ) AS QueryText, QUOTENAME(DB_NAME(r.database_id)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(est.objectid, est.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(est.objectid, est.dbid)) AS ProcedureName, tdb.UserObjectAllocated * 8 AS CurrentUserAllocatedKB, (t.user_objects_alloc_page_count + tdb.UserObjectAllocated) * 8 AS TotalUserAllocatedKB, tdb.UserObjectDeallocated * 8 AS UserDeallocatedKB, (t.user_objects_dealloc_page_count + tdb.UserObjectDeallocated) * 8 AS TotalUserDeallocatedKB, tdb.InternalObjectAllocated * 8 AS InternalAllocatedKB, (t.internal_objects_alloc_page_count + tdb.InternalObjectAllocated) * 8 AS TotalInternalAllocatedKB, tdb.InternalObjectDeallocated * 8 AS InternalDeallocatedKB, (t.internal_objects_dealloc_page_count + tdb.InternalObjectDeallocated) * 8 AS TotalInternalDeallocatedKB, r.reads AS RequestedReads, r.writes AS RequestedWrites, r.logical_reads AS RequestedLogicalReads, r.cpu_time AS RequestedCPUTime, s.is_user_process AS IsUserProcess, s.original_login_name AS OriginalLoginName, s.nt_domain AS NTDomain, s.nt_user_name AS NTUserName, s.[host_name] AS HostName, r.start_time AS StartTime, s.login_time AS LoginTime, s.last_request_start_time AS LastRequestedStartTime, s.last_request_end_time AS LastRequestedEndTime FROM sys.dm_db_session_space_usage AS t INNER JOIN sys.dm_exec_sessions AS s ON s.session_id = t.session_id LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id LEFT JOIN ( SELECT _tsu.session_id, _tsu.request_id, SUM(_tsu.user_objects_alloc_page_count) AS UserObjectAllocated, SUM(_tsu.user_objects_dealloc_page_count) AS UserObjectDeallocated, SUM(_tsu.internal_objects_alloc_page_count) AS InternalObjectAllocated, SUM(_tsu.internal_objects_dealloc_page_count) AS InternalObjectDeallocated FROM tempdb.sys.dm_db_task_space_usage AS _tsu GROUP BY _tsu.session_id, _tsu.request_id ) AS tdb ON tdb.session_id = r.session_id AND tdb.request_id = r.request_id OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS est WHERE t.session_id != @@SPID AND (tdb.UserObjectAllocated - tdb.UserObjectDeallocated + tdb.InternalObjectAllocated - tdb.InternalObjectDeallocated) != 0 OPTION (RECOMPILE);
|
|
|
|
|