mssql 监控磁盘空间告警实现方法
程序员文章站
2023-02-19 23:43:10
这几天突然有个想法：希望能够自动监控、收集数据库服务器的磁盘容量信息，当达到一个阀值后，自动发送告警邮件给dba，将数据库磁盘详细信息告知dba，提醒dba做好存储规划计划...
这几天突然有个想法：希望能够自动监控、收集数据库服务器的磁盘容量信息，当达到一个阀值后，自动发送告警邮件给dba，将数据库磁盘详细信息告知dba，提醒dba做好存储规划计划，初步的想法是通过作业调用存储过程来实现（每天调用一次），这样避免了我每天每台数据库服务器都上去检查一下，尤其是手头的数据库服务器n多的情况，这样可以避免我每天浪费无谓的时间。如果大家有更好的建议和方法，欢迎指点一二，我整理、修改了三个存储过程如下：
存储过程1：sp_diskcapacityalert1.prc
说明：需要通过调用ole 自动存储过程获取磁盘信息，而这些组件，基于服务器的安全配置，通常是禁用的，我们在存储过程通过sp_configure开启这个服务，调用服务完毕后，又通过sp_configure禁用该服务。另外，数据库服务器都位于内网，因此安全问题应该不大。
复制代码 代码如下:
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 : 获取数据库所在服务器的磁盘容量，当达到阀值是，发送告警邮件，提醒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
)
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;
--创建临时表保存服务器磁盘容量信息
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 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', --指定你要发送到的邮箱
@subject = '服务器磁盘空间告警',
@body = @htmlcontent,
@body_format = 'html' ;
end
drop table #diskcapacity;
return;
go
存储过程2：sp_diskcapacityalert2.prc
说明：需要启用xp_cmdshell来获取磁盘信息，关于xp_cmdshell安全隐患，一般该功能都是禁用的。
复制代码 代码如下:
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 : 获取数据库所在服务器的磁盘容量，当达到阀值时，发送告警邮件，提醒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
)
as
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 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', --指定你要发送到的邮箱
@subject = '服务器磁盘空间告警',
@body = @htmlcontent,
@body_format = 'html' ;
end
end
go
存储过程3：sp_diskcapacityalert3.prc
说明：这个存储过程不用上面两个有安全隐患的存储过程，但是获取不到磁盘的总体信息，就不能通过一个阀值来告警，只能设置当磁盘剩余多少空间时，产生告警邮件。
复制代码 代码如下:
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 : 获取数据库所在服务器的磁盘容量，当某个磁盘剩余容量低于某个值时，发送告警邮件，
-- 提醒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
)
as
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 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', --指定你要发送到的邮箱
@subject = '服务器磁盘空间告警',
@body = @htmlcontent,
@body_format = 'html' ;
end
end
go
作者：潇湘隐者
出处：http://www.cnblogs.com/kerrycode/