• Tempdb--查看tempdb使用的脚本


    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

    截图:

  • 相关阅读:
    软件工程学习总结
    第13次作业--邮箱的正则表达式
    第12次作业--你的生日
    第11次作业--字符串处理
    第10次作业
    找回感觉的练习
    基础网络流学习笔记
    卷积定理的证明
    快速xxx变换相关
    主席树相关
  • 原文地址:https://www.cnblogs.com/TeyGao/p/3519602.html
Copyright © 2020-2023  润新知