当磁盘剩余空间小于百分二十的时候发报警邮件的存储过程。

最新推荐文章于 2023-07-29 22:27:55 发布

原创最新推荐文章于 2023-07-29 22:27:55 发布 · 5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#磁盘 #存储 #output #table #insert #disk

SQL2008 专栏收录该内容

10 篇文章

订阅专栏

这是一个SQL存储过程，用于监控磁盘空间。当磁盘剩余空间低于20%时，该过程会通过电子邮件发送报警。它首先通过`usp_sendspacealert`获取磁盘信息，然后使用`xp_cmdshell`执行外部命令获取详细空间数据。如果发现有磁盘空间不足的情况，存储过程将构造HTML格式的邮件内容并通过`msdb.dbo.sp_send_dbmail`发送邮件。

部署运行你感兴趣的模型镜像

 create  proc usp_sendspacealert
 
 as
--此存储需要打开xp_cmdshell执行权限
--定义磁盘剩余空间表
declare @y table (DRIVE varchar(2),FREE_MB INT)
insert into @y exec  master.dbo.xp_fixeddrives
 
--定义磁盘总空间临时信息表
--此步骤通过dos命令diskpart调用输入结果存入临时表中，需要在C:\目录中存在a.txt文件，且a.txt文件内容为"list vol"
declare @x table (volinfo varchar(200))
insert into @x exec xp_cmdshell ＇diskpart < c:\a.txt |findstr "Partition" ＇
delete from @x where volinfo is NULL
 
 
--定义磁盘空间表
declare @z table (DRIVENAME varchar(2),DISKSPACE_GB INT)
insert INTo @z
select substring (volinfo,charindex(＇volume＇,volinfo,0)+13,1) AS DRIVENAME,
substring (volinfo,charindex(＇partition＇,volinfo,0)+9,
charindex (＇GB＇,volinfo,0)-charindex (＇partition＇,volinfo,0)-9)AS DISKSPACE_GB from @x

--判断是否有剩余空间小于20%的磁盘,如果存储就将结果集以HTML格式发出
if exists

(select a.DRIVE,a.FREE_MB/1024 as FREE_GB,b.DISKSPACE_GB,a.FREE_MB/1024*1.0/b.DISKSPACE_GB AS Proportion
FROM @y a,@z  b WHERE a.DRIVE=b.DRIVENAME and a.FREE_MB/1024*1.0/b.DISKSPACE_GB <0.2)--此值为判断标准，可以根据需求修改

begin
 declare @xml NVARCHAR(MAX)
 declare @body NVARCHAR(MAX)
 declare @subj varchar(200)
 set @xml=cast((
 select a.DRIVE as ＇td＇,＇＇,a.FREE_MB/1024  as ＇td＇,＇＇,b.DISKSPACE_GB as ＇td＇,＇＇,a.FREE_MB/1024*1.0/b.DISKSPACE_GB as ＇td＇,＇＇
 FROM @y a,@z  b WHERE a.DRIVE=b.DRIVENAME and a.FREE_MB/1024*1.0/b.DISKSPACE_GB <0.2
 FOR XML PATH(＇tr＇), ELEMENTS) AS NVARCHAR(MAX))

 set @subj=@@servername+convert (varchar(50),convert(varchar(5),YEAR(getdate()))+＇Year＇+convert(varchar(2),month(getdate()))+＇Month＇+convert(varchar(2),day(getdate())))+＇Disk space alert info＇
 --print @a
 SET @body =＇<html><H1>＇+convert(varchar(5),YEAR(getdate()) )+＇Year＇+convert(varchar(2),month(getdate()))+＇Month＇+convert(varchar(2),day(getdate()))+＇day＇+＇List</H1><body bgcolor=#E3F6CE><table border =1>
 <tr><th>Drive</th><th>FREE_GB</th><th>DISKSPACE_GB</th><th>Proportion</th></tr>＇
 if @xml is not null SET @body = @body + @xml +＇</table></body></html>＇

 exec msdb.dbo.sp_send_dbmail
 @recipients =N＇meng@abc.com＇,
 @body = @body,
 @body_format =＇HTML＇,
 @subject =@subj,
 @profile_name =＇dbalert＇

end
 

以下存储不需要额外在存储上建立文本文件，以下存储过程使用了WMI的取磁盘信息功能。

 

 


use master
GO
if object_id(＇sp_disk＇) is not null
 drop procedure sp_disk
GO

create procedure sp_disk
 
as
/**********************
{VER  EDITOR   DATE  COMMENT }
{ver 1.0 summer.yang  2009-06-24 create procedure }

REMARK:
 1.select logicl disk space
EXAMPLE:
 exec sp_disk
************************/
set nocount on
declare @return int,@rowcount int

BEGIN  TRY

--drop table @logicaldisk
--create table @logicaldisk(DeviceID sysname,FreeSpace varchar(20) , Size varchar(20),VolumeName varchar(200))
declare @logicaldisk table(DeviceID sysname,FreeSpace varchar(200) , Size varchar(200),VolumeName varchar(200))

--drop table @tbl
--create table @tbl (id int identity,output varchar(max))
declare @tbl table(id int identity,output varchar(max))
insert into @tbl exec /*@return=*/xp_cmdshell N＇wmic logicaldisk where "Size>0" get DeviceID,FreeSpace,Size,VolumeName /value ＇
select @rowcount=@@rowcount
if @rowcount=0 raiserror 50505  ＇Error: None rows!＇
if @return<>0 raiserror 50605  ＇Error: wmic logicaldisk failed!＇


insert into @logicaldisk(DeviceID,FreeSpace  ,Size,VolumeName)
select  DeviceID,FreeSpace  ,Size,VolumeName
 from
(select id-2 as id,ltrim(rtrim(replace(replace(output,＇DeviceID=＇,＇＇),char(13),＇＇))) as DeviceID from @tbl where patindex(＇%DeviceID=%＇,output)<>0) a
 left join
(select id-3 as id ,substring(output,charindex(＇=＇,output)+1,charindex(char(13),output)-charindex(＇=＇,output)-1) as FreeSpace from @tbl where patindex(＇%FreeSpace=%＇,output)<>0 ) b
on a.id =b.id
 left join
(select id-4 as id ,substring(output,charindex(＇=＇,output)+1,charindex(char(13),output)-charindex(＇=＇,output)-1) as Size from @tbl where patindex(＇%Size=%＇,output)<>0 )  c
on a.id=c.id
 left join
(select id-5 as id ,ltrim(rtrim(replace(replace(output,＇VolumeName=＇,＇＇),char(13),＇＇))) as VolumeName from @tbl where patindex(＇%VolumeName=%＇,output)<>0 ) d
on a.id=d.id

END TRY

BEGIN CATCH
    declare @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;
    select    @ErrorMessage = ERROR_MESSAGE()+char(10)
      +＇ErrorProcedure : ＇+isnull(ERROR_PROCEDURE(),＇＇)+char(10)
      +＇ErrorLine : ＇+ltrim(str(ERROR_LINE()))
    , @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    RAISERROR (@ErrorMessage,  @ErrorSeverity, @ErrorState  );
END CATCH;

print ＇＇

select DeviceID as Drive,VolumeName as Label
 ,round(convert(money,FreeSpace)/1024/1024/1024,2) as Free_GB
 ,round(convert(money,Size)/1024/1024/1024,2)  as Size_GB
 ,round((convert(money,Size)-convert(money,FreeSpace))*100/convert(money,Size),0) as P_Used
 --,DeviceID+isnull(＇[＇+VolumeName+＇]＇,＇＇) as name
 from @logicaldisk

--sp_disk
GO

您可能感兴趣的与本文相关的镜像

Stable-Diffusion-3.5

图片生成

Stable-Diffusion

Stable Diffusion 3.5 (SD 3.5) 是由 Stability AI 推出的新一代文本到图像生成模型，相比 3.0 版本，它提升了图像质量、运行速度和硬件效率