监控数据库占用容量及存储剩余容量

xiaoxiao2021-03-01  15

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

转载请注明原文地址: https://www.6miu.com/read-3850279.html

最新回复(0)