mssql 監(jiān)控磁盤空間告警實(shí)現(xiàn)方法
更新時間：2013年08月04日 22:36:39 作者：
需要通過調(diào)用OLE 自動存儲過程獲取磁盤信息，而這些組件，基于服務(wù)器的安全配置，通常是禁用的，我們在存儲過程通過sp_configure開啟這個服務(wù)，調(diào)用服務(wù)完畢后，又通過sp_configure禁用該服務(wù)。另外，數(shù)據(jù)庫服務(wù)器都位于內(nèi)網(wǎng)，因此安全問題應(yīng)該不大
這幾天突然有個想法：希望能夠自動監(jiān)控、收集數(shù)據(jù)庫服務(wù)器的磁盤容量信息，當(dāng)達(dá)到一個閥值后，自動發(fā)送告警郵件給DBA，將數(shù)據(jù)庫磁盤詳細(xì)信息告知DBA，提醒DBA做好存儲規(guī)劃計(jì)劃，初步的想法是通過作業(yè)調(diào)用存儲過程來實(shí)現(xiàn)（每天調(diào)用一次），這樣避免了我每天每臺數(shù)據(jù)庫服務(wù)器都上去檢查一下，尤其是手頭的數(shù)據(jù)庫服務(wù)器N多的情況，這樣可以避免我每天浪費(fèi)無謂的時間。如果大家有更好的建議和方法，歡迎指點(diǎn)一二，我整理、修改了三個存儲過程如下：
存儲過程1：SP_DiskCapacityAlert1.prc
說明：需要通過調(diào)用OLE 自動存儲過程獲取磁盤信息，而這些組件，基于服務(wù)器的安全配置，通常是禁用的，我們在存儲過程通過sp_configure開啟這個服務(wù)，調(diào)用服務(wù)完畢后，又通過sp_configure禁用該服務(wù)。另外，數(shù)據(jù)庫服務(wù)器都位于內(nèi)網(wǎng)，因此安全問題應(yīng)該不大。
復(fù)制代碼 代碼如下:
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 : 獲取數(shù)據(jù)庫所在服務(wù)器的磁盤容量，當(dāng)達(dá)到閥值是，發(fā)送告警郵件，提醒DBA做好存儲規(guī)劃計(jì)劃
/******************************************************************************************************************
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;
--創(chuàng)建臨時表保存服務(wù)器磁盤容量信息
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 ='數(shù)據(jù)庫磁盤容量告警(告警級別3)'
ELSE IF @FreeRat >=5 AND @FreeRat <=10
SET @EmailHead ='數(shù)據(jù)庫磁盤容量告警(告警級別4)'
ELSE
SET @EmailHead ='數(shù)據(jù)庫磁盤容量告警(告警級別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 style="color:#FF0000; text-align:center;font-size:14px">' + @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', --指定你要發(fā)送到的郵箱
@subject = '服務(wù)器磁盤空間告警',
@body = @HtmlContent,
@body_format = 'HTML' ;
END
DROP TABLE #DiskCapacity;
RETURN;
GO
存儲過程2：SP_DiskCapacityAlert2.prc
說明：需要啟用xp_cmdshell來獲取磁盤信息，關(guān)于xp_cmdshell安全隱患，一般該功能都是禁用的。
復(fù)制代碼 代碼如下:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.sp_diskcapacity_alert2') IS NOT NULL
DROP PROCEDURE dbo.sp_diskcapacity_alert2;
GO
--==================================================================================================================
-- ProcedureName : sp_diskcapacity_alert2
-- Author : Kerry
-- CreateDate : 2013-05-02
-- Description : 獲取數(shù)據(jù)庫所在服務(wù)器的磁盤容量，當(dāng)達(dá)到閥值時，發(fā)送告警郵件，提醒DBA做好存儲規(guī)劃計(jì)劃
/******************************************************************************************************************
Modified Date Modified User Version Modified Reason
2013-05-6 Kerry V01.00.00 修改HTML輸出樣式.以及磁盤容量輸出改為GB
*******************************************************************************************************************/
--==================================================================================================================
CREATE PROCEDURE [dbo].[sp_diskcapacity_alert2]
(
@Threshold NUMERIC
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @HtmlContent NVARCHAR(MAX) ;
DECLARE @FreeRat NUMERIC;
DECLARE @EmailHead VARCHAR(200);
--創(chuàng)建臨時表保存服務(wù)器磁盤容量信息
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 ='數(shù)據(jù)庫磁盤容量告警(告警級別3)'
ELSE IF @FreeRat >=5 AND @FreeRat <=10
SET @EmailHead ='數(shù)據(jù)庫磁盤容量告警(告警級別4)'
ELSE
SET @EmailHead ='數(shù)據(jù)庫磁盤容量告警(告警級別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 style="color:#FF0000; text-align:center;font-size:14px">' + @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', --指定你要發(fā)送到的郵箱
@subject = '服務(wù)器磁盤空間告警',
@body = @HtmlContent,
@body_format = 'HTML' ;
END
END
GO
存儲過程3：SP_DiskCapacityAlert3.prc
說明：這個存儲過程不用上面兩個有安全隱患的存儲過程，但是獲取不到磁盤的總體信息，就不能通過一個閥值來告警，只能設(shè)置當(dāng)磁盤剩余多少空間時，產(chǎn)生告警郵件。
復(fù)制代碼 代碼如下:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'dbo.sp_diskcapacity_alert3') IS NOT NULL
DROP PROCEDURE dbo.sp_diskcapacity_alert3;
GO
--==================================================================================================================
-- ProcedureName : sp_diskcapacity_alert3
-- Author : Kerry
-- CreateDate : 2013-05-02
-- Description : 獲取數(shù)據(jù)庫所在服務(wù)器的磁盤容量，當(dāng)某個磁盤剩余容量低于某個值時，發(fā)送告警郵件，
-- 提醒DBA做好存儲規(guī)劃計(jì)劃
/******************************************************************************************************************
Modified Date Modified User Version Modified Reason
2013-05-6 Kerry V01.00.00 修改HTML輸出樣式.以及磁盤容量輸出改為GB
*******************************************************************************************************************/
--==================================================================================================================
CREATE PROCEDURE [dbo].[sp_diskcapacity_alert3]
(
@DiskCapacity FLOAT
)
AS
BEGIN
DECLARE @FreeSize INT;
DECLARE @EmailHead VARCHAR(200);
DECLARE @HtmlContent NVARCHAR(MAX) ;
--創(chuàng)建臨時表保存服務(wù)器磁盤容量信息
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 = '數(shù)據(jù)庫磁盤容量告警(告警級別3)'
ELSE
IF @FreeSize >= 0.5
AND @FreeSize <= 1
SET @EmailHead = '數(shù)據(jù)庫磁盤容量告警(告警級別4)'
ELSE
SET @EmailHead = '數(shù)據(jù)庫磁盤容量告警(告警級別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 style="color:#FF0000; text-align:center;font-size:14px">'
+ @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', --指定你要發(fā)送到的郵箱
@subject = '服務(wù)器磁盤空間告警',
@body = @HtmlContent,
@body_format = 'HTML' ;
END
END
GO
作者：瀟湘隱者
出處：http://www.cnblogs.com/kerrycode/
您可能感興趣的文章:
- SqlServer如何通過SQL語句獲取處理器(CPU)、內(nèi)存（Memory）、磁盤（Disk）以及操作系統(tǒng)相關(guān)信息
- ubuntu下磁盤空間不足導(dǎo)致mysql無法啟動的解決方法
- Mysql存儲引擎MyISAM的常見問題（表損壞、無法訪問、磁盤空間不足）
- lnmp下如何關(guān)閉Mysql日志保護(hù)磁盤空間
- 幾個縮減MySQL以節(jié)省磁盤空間的建議
- Mysql InnoDB刪除數(shù)據(jù)后釋放磁盤空間的方法
- MySQL中查詢所有數(shù)據(jù)庫占用磁盤空間大小和單個庫中所有表的大小的sql語句
- SQL Server獲取磁盤空間使用情況
相關(guān)文章
SQL Server 2008數(shù)據(jù)庫誤刪數(shù)據(jù)如何進(jìn)行數(shù)據(jù)恢復(fù)
這篇文章主要為大家詳細(xì)介紹了SQL Server 2008數(shù)據(jù)庫誤刪數(shù)據(jù)如何進(jìn)行數(shù)據(jù)恢復(fù)的方法，感興趣的小伙伴們可以參考一下2016-05-05
SQL Server 2008中的代碼安全（六） 對稱密鑰加密
證書和非對稱密鑰使用數(shù)據(jù)庫級的內(nèi)部公鑰加密數(shù)據(jù)，并且使用數(shù)據(jù)庫級內(nèi)部私鑰解密數(shù)據(jù)。而對稱密鑰相對簡單，它們包含一個同時用來加密和解密的密鑰。2011-06-06
Java連接sqlserver2008數(shù)據(jù)庫代碼
這篇文章主要介紹了Java連接sqlserver2008代碼,需要的朋友可以參考下2017-04-04
sql server 2008數(shù)據(jù)庫連接字符串大全
這篇文章主要介紹了sql server 2008數(shù)據(jù)庫的連接字符串大全,需要的朋友可以參考下2014-03-03
項(xiàng)目升級數(shù)據(jù)庫由SQL2000升級到2008R2，今天對數(shù)據(jù)庫表進(jìn)行碎片掃描，發(fā)現(xiàn)有些表碎片較大，于是決定重建索引，MSDN聯(lián)機(jī)幫助是最好的老師，將相關(guān)腳本摘錄備后查2014-07-07
SQLServer2008新實(shí)例遠(yuǎn)程數(shù)據(jù)庫鏈接問題(sp_addlinkedserver)
這篇文章主要介紹了SQLServer2008新實(shí)例遠(yuǎn)程數(shù)據(jù)庫鏈接問題(sp_addlinkedserver),需要的朋友可以參考下2017-05-05
這篇文章主要介紹了SQL之patindex函數(shù)的用法案例詳解,本篇文章通過簡要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-08-08
SQL Server使用一個語句塊批量插入多條記錄的三種方法
本文介紹了三種不同的方法向數(shù)據(jù)庫中一次插入多條記錄的方法，第三種方法是SQL Server2008中特有的，大家體驗(yàn)一下吧。2016-05-05
SQL Server 2008網(wǎng)絡(luò)協(xié)議深入理解
可以通過展開SQL Server 2008網(wǎng)絡(luò)配置節(jié)點(diǎn)進(jìn)行服務(wù)器協(xié)議的配置，需要了解更多的朋友可以參考本文2012-11-11
這篇文章主要介紹了sql server 2008 忘記sa密碼的解決方法,需要的朋友可以參考下2016-12-12