~楓花雪岳~: [SQL] 利用 T-SQL 監測硬碟空間

星期五, 1月 04, 2013

[SQL] 利用 T-SQL 監測硬碟空間

這篇文章介紹兩種監測硬碟空間的方法

利用 xp_fixeddrives 搭配 Automation

xp_fixeddrives：找出本機硬碟還有多少可用空間，但無法抓取網路磁碟，是一個 undocumented extended stored procedure。
Automation：利用 FileSystemObject 來抓取本機硬碟總空間。

要使用 Automation 必須先開啟伺服器選項 Ole Automation Procedures

在 Facet 內啟用（啟動一）

利用 T-SQL 語法啟動（啟動二）

sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ole Automation Procedures', 1
reconfigure
GO

執行語法

CREATE PROCEDURE [dbo].[uspDiskSpaceAlert]
AS
  BEGIN

    DECLARE @hr int
    DECLARE @fso int
    DECLARE @drive char(1)
    DECLARE @fso_Method varchar(255) 
    DECLARE @totalspace bigint
    DECLARE @description varchar(500)
    DECLARE @text  NVARCHAR(MAX)

    SET @text = ''

    DECLARE @space table (drive char(1),freespace int,totalspace int)

    INSERT INTO @space (drive,freespace) 
    EXEC master.dbo.xp_fixeddrives
 
    EXEC @hr = master.dbo.sp_OAcreate 'Scripting.FileSystemObject',@fso OUTPUT
    IF @hr = 0
      BEGIN

        DECLARE  curDrivers CURSOR FAST_FORWARD 
        FOR 
          (
            SELECT drive FROM @space
          )
        OPEN curDrivers
        FETCH NEXT FROM curDrivers INTO @drive
        WHILE (@@Fetch_Status = 0)
          BEGIN
 
            SET @fso_Method = 'Drives("' + @drive + ':").TotalSize'
            EXEC @hr = SP_OAMethod @fso , @fso_method , @totalspace OUTPUT

            IF @hr = 0
              BEGIN
                UPDATE @space 
                SET totalspace = @totalspace / (1024.0 * 1024.0)
                WHERE drive = @drive
              END
            ELSE
              EXEC sp_OAGetErrorInfo @fso , '' , @description OUTPUT
   
            FETCH NEXT FROM curDrivers INTO @drive
          END

        CLOSE curDrivers
        DEALLOCATE curDrivers
    
        EXEC @hr=sp_OADestroy @fso
        IF @hr <> 0
          EXEC sp_OAGetErrorInfo @fso , '' , @description OUTPUT
      END
    ELSE 
      EXEC sp_OAGetErrorInfo @fso , '' , @description OUTPUT

    IF @hr <> 0
      SET @text = 'Automation Fail. Error info is ' + @description
    ELSE
      BEGIN
        IF EXISTS
          (
            SELECT 1
            FROM @space
            WHERE 1.0 * freespace / totalspace < 0.2 -- 硬碟空間小於20%
          )
            SET @text = 'Disk Space is less than 20% ' 
      END

    IF @text <> ''
      BEGIN
        EXEC msdb.dbo.sp_send_dbmail 
          @profile_name = 'Database Mail Profile' ,
          @recipients='DBA@gmail.com' ,
          @subject = 'Disk Space Alert',
          @body = @text ,
          @body_format = 'TEXT'
      END
  END

事實上利用 xp_fixeddrives 就可以達到監控硬碟空間的需求，並一定要用到 Automation，除非一定要知道硬碟總空間。

sys.dm_os_volume_stats DMF

sys.dm_os_volume_stats DMF 在 SQL Server 2008 R2 SP1 時推出，主要是找出 DB 檔案所在磁碟空間的空間大小

IF EXISTS
  (
    SELECT 1
    FROM sys.master_files mf
        CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
    WHERE (1.0 * dovs.available_bytes / dovs.total_bytes) < 0.2 -- 硬碟空間小於20%
  )
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Database Mail Profile' ,
      @recipients='DBA@gmail.com' ,
      @subject = 'Disk Space Alert',
      @body = 'Disk Space is less than 20%' ,
      @body_format = 'TEXT'
END

以上兩種方法搭配 DatabaseMail 和 SQL Server Agent ，依實務需求設定多久檢查硬碟空間，當空間不足時，可以發 mail 通知，另外儲存硬碟空間資料，還可以用來分析資料增長幅度，當成規劃硬碟依據。

Super SQL Server 楊老師分享 - 利用 PowerShell 來抓取相關資訊

~楓花雪岳~

星期五, 1月 04, 2013

[SQL] 利用 T-SQL 監測硬碟空間

沒有留言:

張貼留言