这几天突然有个想法：希望能够自动监控、收集数据库服务器的磁盘容量信息，当达到一个阀值后，自动发送告警邮件给DBA，将数据库磁盘详细信息告知DBA，提醒DBA做好存储规划计划，初步的想法是通过作业调用存储过程来实现（每天调用一次），这样避免了我每天每台数据库服务器都上去检查一下，尤其是手头的数据库服务器N多的情况，这样可以避免我每天浪费无谓的时间。如果大家有更好的建议和方法，欢迎指点一二，我整理、修改了三个存储过程如下：
存储过程1：SP_DiskCapacityAlert1.prc
说明：需要通过调用OLE 自动存储过程获取磁盘信息，而这些组件，基于服务器的安全配置，通常是禁用的，我们在存储过程通过sp_configure开启这个服务，调用服务完毕后，又通过sp_configure禁用该服务。另外，数据库服务器都位于内网，因此安全问题应该不大。
代码如下:
USE master;
GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOIF 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
)
ASSET 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 DiskCDOPEN CR_DiskInfo;FETCH NEXT FROM CR_DiskInfo INTO @DriveInfoWHILE @@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 @DriveInfoENDCLOSE CR_DiskInfo
DEALLOCATE CR_DiskInfo;EXEC @Result=sp_OADestroy @objectInfoEXEC sp_configure 'show advanced options',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''
'
GOSET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOIF 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
)
ASSET 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 DiskCDOPEN CR_DiskInfo;FETCH NEXT FROM CR_DiskInfo INTO @DriveInfoWHILE @@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 @DriveInfoENDCLOSE CR_DiskInfo
DEALLOCATE CR_DiskInfo;EXEC @Result=sp_OADestroy @objectInfoEXEC sp_configure 'show advanced options',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'
'
+ N'
| 总大小(GB) | 已用空间(GB) | 剩余空间(GB) | '已用比例(%) | 剩余比例(%) | ' +
|---|
| 剩余空间(GB) | ' + N''
|---|
