[SQL] 標準報表 - 磁碟使用量

此報表可以觀察資料檔案、交易記錄檔案磁碟空間使用情形和資料庫自動成長／自動壓縮的事件。

標示 1：資料檔空間使用量和交易記錄空間使用量，是指 mdf 和 ldf 檔案佔的硬碟空間。
標示 2：資料 / 記錄檔自動成長 / 自動壓縮事件。
標示 3：使用空間，資料（Data）在［標示1 資料檔空間使用量（mdf）］的使用量。

• 資料 / 記錄檔自動成長 / 自動壓縮事件
1. 自動成長頻率過於頻繁，可以考慮增加自動成長的大小，最後是能一次規劃 3 - 5 年的資料量，避免自動成長。
2. 自動成長預設是開啟，建議不要關閉它，避免資料爆增等突發狀況，而造成 SQL Server 無法正常運作。
3. 自動壓縮觸發條件是當未使用空間佔整體使用空間的百分之二十五時，SQL Server 就會自動啟用壓縮功能。
4. 自動壓縮預設關閉，建議不要開啟，避免自動成長後，又因為自動壓縮觸發，SQL Server 一直反覆執行兩動作。
5. 自動成長或自動壓縮都會讓硬碟空間不連續，造成 Disk Logical Read 低落，也可能會造成 Blocking 或逾時等問題，要避免發生。

• 利用 T-SQL 語法查詢資料和記錄檔案的資料使用量

sp_MSforeachdb 和 DBCC SHOWFILESTATS 是 undocument。

DECLARE @DBSpace TABLE
  (
      file_id int , 
      file_group_id int , 
      total_extents int , 
      used_extents int , 
      logical_file_name varchar(500) collate database_default , 
      physical_file_name varchar(500) collate database_default
  )

DECLARE @LogSpace TABLE 
  (
      DBName varchar(500) , 
      LogSize float , 
      LogSpaceUsed float , 
      [Status] int
  )

INSERT INTO @DBSpace EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC (''DBCC SHOWFILESTATS'')'
INSERT INTO @LogSpace EXECUTE('DBCC SQLPERF(''LOGSPACE'')')

SELECT 
    L.DBName ,
    D.total_extents * 64.0 / 1024 AS N'資料檔案使用硬碟空間(MB)' ,
    D.used_extents  * 64.0 / 1024 AS N'資料實際使用空間(MB)',
    L.LogSize AS N'交易檔案使用硬碟空間(MB)' ,
    (L.LogSize * LogSpaceUsed / 100) N'交易記錄檔實際使用空間(MB)'
FROM @DBSpace AS D
    JOIN @LogSpace AS L ON D.logical_file_name = L.DBName
    JOIN sys.databases AS S ON L.DBName = S.Name
WHERE S.database_id > 4 
  AND CHARINDEX('report',S.Name) = 0

• 利用 T-SQL 檢查 SQL Server 是否觸發自動成長或自動壓縮，並發信通知 DBA

資料庫自動成長或自動壓縮被觸發時，會被記錄在預設追蹤（Default Trace）內。

CREATE PROCEDURE uspAutoGrowCheck
AS
  BEGIN

      DECLARE @curr_tracefilename varchar(500) ; 
      DECLARE @base_tracefilename varchar(500) ; 
      DECLARE @indx int ;

      SELECT @curr_tracefilename = [path] FROM sys.traces WHERE is_default = 1 ; 
      SET @curr_tracefilename = REVERSE(@curr_tracefilename);
      SELECT @indx  = PATINDEX('%\%', @curr_tracefilename) ;
      SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;
      SET @base_tracefilename = LEFT(@curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc' ;  

      IF EXISTS
        (
            SELECT 1
            FROM fn_trace_gettable( @base_tracefilename, default )
            WHERE 
              ( 
                -- 92 Data File Auto Grow
                -- 93 Log File Auto Grow
                -- 94 Data File Auto Shrink
                -- 95 Log File Auto Shrink
                EventClass >=  92 AND EventClass <=  95 
              )
              AND 
              (
                -- 判斷昨天有沒有發生
                StartTime >= DATEADD(DD,-1,CAST(CONVERT(char(10),GETDATE(),112) AS datetime) AND 
                StartTime < CAST(CONVERT(char(10),GETDATE(),112) AS datetime)
              ) 
        )
            BEGIN

              EXEC msdb.dbo.sp_send_dbmail
                @profile_name = 'Database Mail Profile' ,
                @recipients='DBA@gmail.com' ,
                @subject = 'Auto Grow（Shrink）Alert',
                @body = 'Auto Grow（Shrink）is triggered' ,
                @body_format = 'TEXT'

            END
  END

上述兩 T-SQL 語法搭配 SQL Server Agent

• 定時去記錄資料檔案和記錄檔使用量，可以當成規劃檔案和交易檔案大小的依據
• 監控自動成長和自動壓縮被觸發。

• 參考資料
• MSDN DBCC SQLPERF、顯示資料庫的資料和記錄空間資訊、壓縮交易記錄檔、Database 事件類別目錄
• 顯示資料庫所使用的磁碟空間、資料實際所使用的空間
• SQL Server 中的 Autogrow 和 Autoshrink 設定考量
• SQL Server Database Growth and Autogrowth Settings
• SQL Pass 討論：1、2