介绍
工作停机或数据库无法响应或服务器无法启动,DBA始终处于热状态,今天,我们将讨论磁盘使用和空间监视的一个非常重要的方面。这是DBA管理和监视磁盘使用情况的基本职责之一。
执行下面的语句给出了系统的详细信息,列出了所有可用的驱动器和每个驱动器上的可用空间MB:
EXEC master.dbo.xp_fixeddrives drive MB free----- -----------C 38126D 79906(2 row(s) affected)
DBA每30分钟运行一次此语句并检查系统是否有足够的可用空间是不可行的。尽量少做点工作。
CREATEPROCEDURE [dbo].[MonitorFreeSpace]ASSETNOCOUNTONSETQUOTED_IDENTIFIERONSETANSI_NULLSONBEGINDECLARE@ThreshHoldINTDECLARE@COUNTINTDECLARE@CatchDriveCHAR(1)DECLARE@SubStrVARCHAR(2000)DECLARE@MsgStrVARCHAR(2000)/*We have set the thresh hold limit for our drives as 2 GB*/SET @ThreshHold=2048/*Table variable to host drives & free spaces details*/DECLARE@SpaceInfoTABLE( ID INTIDENTITY(1,1), Drive CHAR(1), MBFreeSpace INT)INSERTINTO@SpaceInfoEXEC xp_fixeddrivesSELECT @COUNT=MIN(ID) FROM@SpaceInfo/*Looping through all the drives */WHILE (@COUNTISNOTNULLAND @COUNT<=(SELECT MAX(ID) FROM@SpaceInfo)) BEGIN/*Checking for the free space on the drive against preset threshhold */IF ((SELECT MBFreeSpace FROM@SpaceInfoWHERE ID=@COUNT)<@ThreshHold) BEGINSELECT @CatchDrive=Drive FROM@SpaceInfoWHERE ID=@COUNT SET @SubStr='SERVER MyServerName - Available Space on _ '+@CatchDrive+ ': Drive below critical limit.'SET @MsgStr='Attention!! The free space on _ '+@CatchDrive+': drive is below the prescribed _ threshold of'+CONVERT(VARCHAR(20),@ThreshHold)+'MB.'/*Send mail to the concerned for ASAP assistance*/EXEC master.dbo.xp_sendmail @recipients =N'abc.xyz@microsoft.com', @subject =@SubStr, @message =@MsgStr ENDSET @SubStr=''SET @MsgStr=''SET @COUNT+=1 /*check for the next drive*/ENDEND
第三种选择是,如果DBA每次引导服务器时都需要扫描此信息,我们可以配置这个SP,使其在每次服务器重新引导时自动执行以下代码段。
EXEC sp_configure 'scan for startup procs',1;RECONFIGURE ;EXEC sp_procoption @ProcName = 'MonitorFreeSpace', @OptionName = 'startup', @OptionValue = '1';
相关文章