USE [khcable_jn]
GO
/****** Object: StoredProcedure [dbo].[MonitoringDBCapacity] Script Date: 07/12/2018 14:43:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*******监控数据库占用容量及存储剩余容量*******/
/* 整体思路
1、先获取服务器所有硬盘的剩余容量（#HDCapacity表）
2、获取要监控数据库文件所在的硬盘符
3、获取设定的阈值（字段@SYKJYZ）
4、在#HDCapacity中查询数据库文件所在的硬盘的容量小于阈值的盘符（放在#HDCapacityTimp表）
5、遍历#HDCapacityTimp表，生成预警信息（字段@Msg）
6、遍历DBA手机号（字段@DBAPhone），将预警信息写入短信表（ShortMessage表）
*/
/*使用方法
将该存储过程加入到作业中即可
例：Exec MonitoringDBCapacity '手机号1,手机号2'
*/
ALTER PROCEDURE [dbo].[MonitoringDBCapacity]
@DBAPhone VARCHAR(200)--DBA手机号，当有多个手机号时用英文逗号（','）分隔
AS
BEGIN
CREATE TABLE #HDCapacity (PF VARCHAR(2),Msg DECIMAL(10,2)); --创建硬盘剩余容量临时表
CREATE TABLE #HDCapacityTimp (PF VARCHAR(2),Msg DECIMAL(10,2),number INT); --硬盘的剩余容量小于阈值的盘
INSERT INTO #HDCapacity Exec master.dbo.xp_fixeddrives; --获取所有硬盘的剩余容量
DECLARE @sql nvarchar(1000)
DECLARE @DBName VARCHAR(100) --要监控的数据库名称
SET @DBName = 'khcable_jn'
DECLARE @SYKJYZ INT --剩余硬盘容量阈值
SELECT @SYKJYZ = SYKJYZ FROM DN_S_SET
DECLARE @row INT --#HDCapacityTimp表的行数
DECLARE @i INT
DECLARE @Msg VARCHAR(200) --预警内容
--获取数据库文件大小（MB）
--SELECT convert(float,SUM(size)) * (8192.0/1024.0)/1024. Size1,LEFT(filename,1) pf from [khcable_jn].dbo.sysfiles GROUP BY LEFT(filename,1)
--获取数据库文件所在硬盘的剩余容量小于阈值的盘
SET @sql='SELECT *,number = row_number() over(order by PF) FROM #HDCapacity WHERE PF IN(
SELECT LEFT(filename,1) pf FROM ['+@DBName+'].dbo.sysfiles GROUP BY LEFT(filename,1)) AND Msg<'+CONVERT(VARCHAR, @SYKJYZ)+'*1024';
INSERT INTO #HDCapacityTimp EXEC SP_EXECUTESQL @sql;
SET @row = @@ROWCOUNT;
SET @i = 1;
IF(@row>0)
BEGIN
SET @Msg = '服务器硬盘容量预警：硬盘';
while @i <= @row
BEGIN
SELECT @Msg =@Msg + '【'+PF+'】' FROM #HDCapacityTimp WHERE number = @i;
SET @i = @i+1;
END
SET @Msg = @Msg+'容量小于'+CONVERT(VARCHAR,@SYKJYZ)+'GB,请尽快处理。';
--将预警信息写入短信表
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @phone VARCHAR(20) --DBA手机号
SET @PointerPrev=1 ;
WHILE (@PointerPrev < LEN(@DBAPhone))
BEGIN
SET @PointerCurr=CharIndex(',',@DBAPhone,@PointerPrev);
IF(@PointerCurr>0)
BEGIN
SET @phone=SUBSTRING(@DBAPhone,@PointerPrev,@PointerCurr-@PointerPrev);
IF(LEN(@phone)=0)
BEGIN
CONTINUE;
end
insert into ShortMessage(MsgContent,RecNumber) values(@Msg,@phone);
SET @PointerPrev = @PointerCurr+1 ;
END
ELSE
BREAK ;
END
set @phone=SUBSTRING(@DBAPhone,@PointerPrev,LEN(@DBAPhone)-@PointerPrev+1);
IF(LEN(@phone)>0)
BEGIN
insert into ShortMessage(MsgContent,RecNumber) values(@Msg,@phone);
END
END
END
