mssql 监控磁盘空间告警实现方法_mssql数据库教程-查字典教程网

摘要：这几天突然有个想法：希望能够自动监控、收集数据库服务器的磁盘容量信息，当达到一个阀值后，自动发送告警邮件给DBA，将数据库磁盘详细信息告知D...

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

存储过程1：SP_DiskCapacityAlert1.prc

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

复制代码代码如下:

USE master;

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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;

--==================================================================================================================

-- 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

)

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'<html>'

+ N'<style type="text/css">'

+ N' td {border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;}'

+ N' table {border:1px solid #9ec9ec; width:100%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px}'

+ N'</style>'

+ N'<H1>' + @EmailHead +'</H1>'

+ N'<table >'

+ N'<tr><th>磁盘盘符</th><th>总大小(GB)</th><th>已用空间(GB)</th><th>剩余空间(GB)</th>'

+ N'<th>已用比例(%)</th><th>剩余比例(%)</th></tr >' +

CAST ( ( SELECT

td = DiskCD , '',

td = STR(TotalSize*1.0/1024,6,2) , '',

td = STR((TotalSize - FreeSize)*1.0/1024,6,2) , '',

td = STR(FreeSize*1.0/1024,6,2) , '',

td = STR(( TotalSize - FreeSize)*1.0/(TotalSize)* 100.0,6,2), '',

td = STR(( FreeSize * 1.0/ ( TotalSize ) ) * 100.0,6,2) , ''

FROM #DiskCapacity

FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table></html>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DataBase_DDL_Event', --指定你自己的profile_name

@recipients='****@163.com', --指定你要发送到的邮箱

@subject = '服务器磁盘空间告警',

@body = @HtmlContent,

@body_format = 'HTML' ;

END

DROP TABLE #DiskCapacity;

RETURN;

存储过程2：SP_DiskCapacityAlert2.prc

说明：需要启用xp_cmdshell来获取磁盘信息，关于xp_cmdshell安全隐患，一般该功能都是禁用的。

复制代码代码如下:

USE [master]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF OBJECT_ID(N'dbo.sp_diskcapacity_alert2') IS NOT NULL

DROP PROCEDURE dbo.sp_diskcapacity_alert2;

--==================================================================================================================

-- ProcedureName : sp_diskcapacity_alert2

-- 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_alert2]

(

@Threshold NUMERIC

)

BEGIN

SET NOCOUNT ON;

DECLARE @HtmlContent NVARCHAR(MAX) ;

DECLARE @FreeRat NUMERIC;

DECLARE @EmailHead VARCHAR(200);

--创建临时表保存服务器磁盘容量信息

CREATE TABLE #DiskCapacity

(

DiskCD CHAR(4) ,

FreeSize INT ,

TotalSize BIGINT

);

INSERT INTO #DiskCapacity

( DiskCD, FreeSize )

EXEC master..xp_fixeddrives;

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

EXEC sp_configure 'show advanced options', 0

RECONFIGURE

CREATE TABLE #DriveInfo1(ID INT IDENTITY(1,1),DiskCD VARCHAR(12));

INSERT INTO #DriveInfo1(DiskCD)

EXEC xp_cmdshell 'wmic LOGICALDISK get name';

CREATE TABLE #DriveInfo2(ID INT IDENTITY(1,1), TotalSize VARCHAR(22));

INSERT INTO #DriveInfo2

( TotalSize )

EXEC xp_cmdshell 'wmic LOGICALDISK get size';

DELETE FROM #DriveInfo1 WHERE ID=1;

DELETE FROM #DriveInfo2 WHERE ID=1;

UPDATE #DriveInfo1 SET DiskCD = REPLACE(DiskCD,':','');

SELECT * FROM #DiskCapacity

UPDATE #DiskCapacity SET TotalSize =(SELECT CAST(LEFT(N.TotalSize, LEN(N.TotalSize)-1) AS BIGINT)/1024/1024 FROM #DriveInfo1 M INNER JOIN #DriveInfo2 N ON M.ID = N.ID

WHERE M.DiskCD IS NOT NULL AND LEN(M.DiskCD) >1 AND #DiskCapacity.DiskCD = LEFT(M.DiskCD, LEN(M.DiskCD)-1))

SELECT * FROM #DiskCapacity

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 0

RECONFIGURE

EXEC sp_configure 'show advanced options', 0

RECONFIGURE

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'<html>'

+ N'<style type="text/css">'

+ N' td {border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;}'

+ N' table {border:1px solid #9ec9ec; width:100%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px}'

+ N'</style>'

+ N'<H1>' + @EmailHead +'</H1>'

+ N'<table >'

+ N'<tr><th>磁盘盘符</th><th>总大小(GB)</th><th>已用空间(GB)</th><th>剩余空间(GB)</th>'

+ N'<th>已用比例(%)</th><th>剩余比例(%)</th></tr >' +

CAST ( ( SELECT

td = DiskCD , '',

td = STR(TotalSize*1.0/1024,6,2) , '',

td = STR((TotalSize - FreeSize)*1.0/1024,6,2) , '',

td = STR(FreeSize*1.0/1024,6,2) , '',

td = STR(( TotalSize - FreeSize)*1.0/(TotalSize)* 100.0,6,2), '',

td = STR(( FreeSize * 1.0/ ( TotalSize ) ) * 100.0,6,2) , ''

FROM #DiskCapacity

FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table></html>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DataBase_DDL_Event', --指定你自己的profile_name

@recipients='konglb@***.com', --指定你要发送到的邮箱

@subject = '服务器磁盘空间告警',

@body = @HtmlContent,

@body_format = 'HTML' ;

END

存储过程3：SP_DiskCapacityAlert3.prc

说明：这个存储过程不用上面两个有安全隐患的存储过程，但是获取不到磁盘的总体信息，就不能通过一个阀值来告警，只能设置当磁盘剩余多少空间时，产生告警邮件。

复制代码代码如下:

USE [master]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF OBJECT_ID(N'dbo.sp_diskcapacity_alert3') IS NOT NULL

DROP PROCEDURE dbo.sp_diskcapacity_alert3;

--==================================================================================================================

-- ProcedureName : sp_diskcapacity_alert3

-- 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_alert3]

(

@DiskCapacity FLOAT

)

BEGIN

DECLARE @FreeSize INT;

DECLARE @EmailHead VARCHAR(200);

DECLARE @HtmlContent NVARCHAR(MAX) ;

--创建临时表保存服务器磁盘容量信息

CREATE TABLE #DiskCapacity

(

DiskCD CHAR(4) ,

FreeSize INT

);

INSERT INTO #DiskCapacity

( DiskCD, FreeSize )

EXEC master..xp_fixeddrives;

SELECT @FreeSize = FreeSize*1.0/1024

FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY FreeSize ASC ) AS RowIndex ,

FreeSize AS FreeSize

FROM #DiskCapacity

) T

WHERE RowIndex = 1 ;

SELECT FreeSize*1.0/1024 FROM #DiskCapacity;

IF @FreeSize <= @DiskCapacity

BEGIN

IF @FreeSize > 1

AND @FreeSize <= 2

SET @EmailHead = '数据库磁盘容量告警(告警级别3)'

ELSE

IF @FreeSize >= 0.5

AND @FreeSize <= 1

SET @EmailHead = '数据库磁盘容量告警(告警级别4)'

ELSE

SET @EmailHead = '数据库磁盘容量告警(告警级别5)'

SET @HtmlContent = +N'<html>' + N'<style type="text/css">'

+ N' td {border:solid #9ec9ec; border-width:0px 1px 1px 0px; padding:4px 0px;}'

+ N' table {border:1px solid #9ec9ec; width:100%;border-width:1px 0px 0px 1px;text-align:center;font-size:12px}'

+ N'</style>'

+ N'<H1>'

+ @EmailHead + '</H1>' + N'<table >'

+ N'<tr><th>磁盘盘符</th><th>剩余空间(GB)</th>' + N'</tr >'

+ CAST(( SELECT td = DiskCD ,

'' ,

td = STR(FreeSize * 1.0 / 1024, 6, 2) ,

FROM #DiskCapacity

FOR

XML PATH('tr') ,

TYPE

) AS NVARCHAR(MAX)) + N'</table></html>' ;

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DataBase_DDL_Event', --指定你自己的profile_name

@recipients='konglb@***.com', --指定你要发送到的邮箱

@subject = '服务器磁盘空间告警',

@body = @HtmlContent,

@body_format = 'HTML' ;

END

作者：潇湘隐者

出处：http://www.cnblogs.com/kerrycode/