GO /****** Object: StoredProcedure [dbo].[usp_GetTempDBUsedSpace] Script Date: 03/05/2014 13:24:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: SQL SERVER DMVS IN ACTIONS -- Create date: -- Description: 查看Tempdb数据库的空间使用情况 -- ============================================= ALTER PROCEDURE [dbo].[usp_GetTempDBUsedSpace] AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --========================================================== SELECT N'查看tempdb数据文件' EXEC(' USE tempdb; DBCC showfilestats ') --========================================================== SELECT N'查看tempdb日志' DECLARE @T TABLE ( DatabaseName NVARCHAR(200), [LoginSize(MB)] FLOAT, [LogSpceUsed(%)] FLOAT, [Status] INT ) INSERT INTO @T([DatabaseName],[LoginSize(MB)],[LogSpceUsed(%)],[Status]) EXEC('DBCC SQLPERF (LOGSPACE)') SELECT * FROM @T T WHERE T.DatabaseName='tempdb' --========================================================== SELECT N'查看Tempdb数据库的空间使用情况' SELECT SUM(user_object_reserved_page_count + internal_object_reserved_page_count + version_store_reserved_page_count + mixed_extent_page_count + unallocated_extent_page_count) * (8.0/1024.0) AS [TotalSizeOfTempDB(MB)] , SUM(user_object_reserved_page_count + internal_object_reserved_page_count + version_store_reserved_page_count + mixed_extent_page_count) * (8.0/1024.0) AS [UsedSpace (MB)] , SUM(unallocated_extent_page_count * (8.0/1024.0)) AS [FreeSpace (MB)], SUM(USER_object_reserved_page_count) * 8.0/1024 AS user_object_MB , SUM(internal_object_reserved_page_count) * 8.0/1024 AS internal_object_MB , SUM(version_store_reserved_page_count) * 8.0/1024 AS version_store_MB FROM sys.dm_db_file_space_usage --========================================================== SELECT N'查看每个会话在tempdb数据库上的空间使用' SELECT CAST(SUM(su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) * (8.0/1024.0) AS DECIMAL(20,3)) AS [SpaceUsed(MB)] , CAST(SUM(su.user_objects_alloc_page_count - su.user_objects_dealloc_page_count + su.internal_objects_alloc_page_count - su.internal_objects_dealloc_page_count) * (8.0/1024.0) AS DECIMAL(20,3)) AS [SpaceStillUsed(MB)] ,SUM(su.user_objects_alloc_page_count) AS user_objects_alloc_page_count ,SUM(su.user_objects_dealloc_page_count) AS user_objects_dealloc_page_count ,SUM(su.internal_objects_alloc_page_count) AS internal_objects_alloc_page_count ,SUM(su.internal_objects_dealloc_page_count) AS internal_objects_dealloc_page_count , su.session_id , ec.connection_id , es.login_name , es.host_name , st.text AS [LastQuery] , ec.last_read , ec.last_write , es.program_name FROM sys.dm_db_session_space_usage su INNER JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id LEFT OUTER JOIN sys.dm_exec_connections ec ON su.session_id = ec.most_recent_session_id OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st WHERE su.session_id > 50 GROUP BY su.session_id, ec.connection_id, es.login_name, es.host_name , st.text, ec.last_read, ec.last_write, es.program_name ORDER BY [SpaceStillUsed(MB)] DESC --========================================================== END
使用:
exec dbo.usp_GetTempDBUsedSpace
截图: