学步园

当SQL Server磁盘空间降低后,自动发邮件通知(作者:Muthusamy Anantha Kumar aka The MAK)
翻译：邹黎鹏
/*
如有疑问联系: zlp321001@hotmail.com(作者:根据自己测试环境,修改了原文章)
*/

经常SQL Sever数据库管理员用数据库"自动增长"的特征在数据库配置中,允许数据库空间自动增长,当数据文件自增长后,虽然它非常好用,但有时候SQL Server 可能要超过磁盘允许空间,这篇文章主要介绍如果磁盘空间不够后，如何用Email通知DBA。

Note:下面的存储过程在SQL2000中测试通过,你的SQL Server 至少能用SMTP发送邮件
(配置参考：http://blog.csdn.net/zlp321002/archive/2005/11/04/523113.aspx)

步骤一：
建立如下存储过程在SQL Server 用于监视磁盘空间。
use master
go
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROCEDURE  usp_diskspace
@Percentagefree int,
@error2 varchar(8000) OUTPUT
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint
SET @MB = 1048576
DECLARE @COUNT int
DECLARE @Maxcount int
DECLARE @error varchar(700)
DECLARE @errordrive char(1)
DECLARE @errortotalspace varchar(20)
DECLARE @errorfreespace varchar(20)
DECLARE @free int
DECLARE @date varchar(100)
declare @query varchar(1300)
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
CREATE TABLE #drives (id int identity(1,1),ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC @query
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB,
 ServerName = replace( @query ,
 'master.dbo.xp_fixeddrives',''),
 FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
set @maxcount =(select max(id) from #drives)
set @count=1
while @count <=@maxcount
begin
select @errortotalspace =
 convert(varchar(20),
 Totalsize),
 @errorfreespace =freespace,
 @free=CAST((FreeSpace/(TotalSize*1.0))*100.0 as int),
 @errordrive=Drive from #drives where id = @count
if @free<@percentagefree
begin
set @error = 'Server =
 '+@@servername+':
 Drive=' + @errordrive+':
 Percentage free=' +convert(varchar(2),
 @free)+'% TotalSpace ='+ @errortotalspace  +'MB :
 FreeSpace ='+ @errorfreespace +'MB :Date =' +@date
set @error2=@error2+@error+char(13)
end
else
begin
set @error = 'Server =
 '+@@servername+':
 Drive=' + @errordrive+':
 Percentage free=' +convert(varchar(2),
 @free)+'% TotalSpace ='+ @errortotalspace  +'MB :
 FreeSpace ='+ @errorfreespace +'MB :Date =' +@date
end
set @count=@count+1
end
DROP TABLE #drives
set @date = convert(varchar(100), getdate(),109)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

步骤二：
配置好数据库邮件发送服务(参考配置(outLook 2000或者 Outlook2003发送邮件配置)

步骤三:
用下面代码,我们能测试存储过程。
(发送邮件格式和相关信息可以参考Help_Onlie,也可以发送附件和群发邮件)

use
master
declare @out varchar(8000)
declare @To varchar(200)
declare @minimumspace int
declare @subject varchar(300)
set @minimumspace = 15
set @To ='zlp321001@hotmail.com'
set @subject = 'Running out of Hard Disk space on the Server: '+@@servername
exec usp_diskspace @minimumspace,@out OUTPUT
if @OUT is not null or ltrim(rtrim(@OUT))<>''
begin
EXEC xp_sendmail @recipients =@To,
                 @message =@out,
                 @subject =@subject
end

参数说明
@To                       是发送对方地址
@Subject                  邮件主题
@minimumspace             磁盘空间最小空间白分比

Note:
请更新所有的参数根据你自己机器环境

一旦以上代码在你查询分析器中执行,这usp_diskspace从你的硬盘驱动收集信息。一旦低于磁盘的最低磁盘空间,将使用usp_send_cdosysemail发送邮件。
如下图：

步骤四：
建立一个计划JOB,每小时去执行步骤三
Note:根据自己环境更新参数。
如下图：

结论：
这文章主要说明当SQL Server 磁盘空间不够后，如何用邮件通知DBA。

作者从FoxMial收到邮件信息如下:

发件人:zlp
收件人:zlp@zehua.com.cn
日期: 2005-11-30 10:40:00
主题: running out of hard disk space on zhe server :TOSHIBA

内容:

Server =
 TOSHIBA:
 Drive=C:
 Percentage free=50% TotalSpace =19712MB :
 FreeSpace =10042MB :Date =11 30 2005 10:40:55:537AM
Server =
 TOSHIBA:
 Drive=F:
 Percentage free=22% TotalSpace =10244MB :
 FreeSpace =2277MB :Date =11 30 2005 10:40:55:537AM
Server =
 TOSHIBA:
 Drive=G:
 Percentage free=27% TotalSpace =8197MB :
 FreeSpace =2264MB :Date =11 30 2005 10:40:55:537AM