这几天突然有个想法：希望能够自动监控、收集数据库服务器的磁盘容量信息，当达到一个阀值后，自动发送告警邮件给DBA，将数据库磁盘详细信息告知DBA，提醒DBA做好存储规划计划，初步的想法是通过作业调用存储过程来实现（每天调用一次），这样避免了我每天每台数据库服务器都上去检查一下，尤其是手头的数据库服务器N多的情况，这样可以避免我每天浪费无谓的时间。如果大家有更好的建议和方法，欢迎指点一二，我整理、修改了三个存储过程如下：

存储过程1：SP_DiskCapacityAlert1.prc

说明：需要通过调用OLE 自动存储过程获取磁盘信息，而这些组件，基于服务器的安全配置，通常是禁用的，我们在存储过程通过sp_configure开启这个服务，调用服务完毕后，又通过sp_configure禁用该服务。另外，数据库服务器都位于内网，因此安全问题应该不大。

复制代码 代码如下:
USE master;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'sp_diskcapacity_alert1') AND OBJECTPROPERTY(id, 'IsProcedure') =1)
    DROP PROCEDURE sp_diskcapacity_alert1;
GO

--==================================================================================================================
--        ProcedureName        :            sp_diskcapacity_alert1
--        Author               :            Kerry   
--        CreateDate           :            2013-05-02
--        Description          :            获取数据库所在服务器的磁盘容量，当达到阀值是，发送告警邮件，提醒DBA做好存储规划计划
/******************************************************************************************************************
    Modified Date        Modified User        Version                    Modified Reason
    2013-05-6               Kerry            V01.00.00          修改HTML输出样式.以及磁盘容量输出改为GB
*******************************************************************************************************************/
--==================================================================================================================
CREATE PROCEDURE [dbo].[sp_diskcapacity_alert1]
(
        @Threshold    NUMERIC
)
AS

SET NOCOUNT ON

 
DECLARE @Result                INT;
DECLARE @objectInfo            INT;
DECLARE @DriveInfo             CHAR(1);
DECLARE @TotalSize             VARCHAR(20);
DECLARE @OutDrive              INT;
DECLARE @UnitMB                BIGINT;
DECLARE @HtmlContent           NVARCHAR(MAX) ;
DECLARE @FreeRat               NUMERIC;
DECLARE @EmailHead             VARCHAR(120);
SET @UnitMB = 1048576;

 

 
--创建临时表保存服务器磁盘容量信息
CREATE TABLE #DiskCapacity
(
    [DiskCD]        CHAR(1) ,
    FreeSize        INT        ,
    TotalSize       INT       
);

INSERT #DiskCapacity
        ([DiskCD], FreeSize )
EXEC master.dbo.xp_fixeddrives;

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE WITH OVERRIDE;

 
EXEC @Result = master.sys.sp_OACreate 'Scripting.FileSystemObject',@objectInfo OUT;

DECLARE CR_DiskInfo CURSOR LOCAL FAST_FORWARD
FOR SELECT  DiskCD FROM #DiskCapacity
ORDER by DiskCD

OPEN CR_DiskInfo;

FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo

WHILE @@FETCH_STATUS=0
BEGIN

    EXEC @Result = sp_OAMethod @objectInfo,'GetDrive', @OutDrive OUT, @DriveInfo

 
    EXEC @Result = sp_OAGetProperty @OutDrive,'TotalSize', @TotalSize OUT

 
    UPDATE #DiskCapacity
    SET TotalSize=@TotalSize/@UnitMB
    WHERE DiskCD=@DriveInfo

    FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo

END

CLOSE CR_DiskInfo
DEALLOCATE CR_DiskInfo;

EXEC @Result=sp_OADestroy @objectInfo

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE;

SELECT @FreeRat =FreeRate
FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY FreeSize / ( TotalSize * 1.0 ) ASC) AS RowIndex,
               CAST(( FreeSize / ( TotalSize * 1.0 ) ) * 100.0 AS INT)          AS FreeRate 

        FROM    #DiskCapacity
     ) T
WHERE RowIndex = 1;

    IF @FreeRat <= @Threshold
        BEGIN

        IF @FreeRat > 10 AND @FreeRat <=20
            SET @EmailHead ='数据库磁盘容量告警(告警级别3)'
        ELSE IF @FreeRat >=5 AND @FreeRat <=10
            SET @EmailHead ='数据库磁盘容量告警(告警级别4)'
        ELSE
            SET @EmailHead ='数据库磁盘容量告警(告警级别5)'

        SET @HtmlContent =
            +   N''
            +   N''
            +   N'

' + @EmailHead +'