天极Yesky
  • 笔记本电脑
    笔记本
  • 台式电脑
    台式机
  • 手机
    手机
  • 电脑硬件DIY
    DIY硬件
  • CPU
    主板
    音箱
  • 硬盘
    显卡
    键鼠
  • 内存光驱
    显示器
    机箱电源

  • 数码相机DC
    数码相机
  • MP3播放器
    MP3/MP4
  • 数码摄像机DV
    摄像机
  • 电脑外设
    外设
  • 网络
    网络
  • 服务器
    服务器
  • 数字家庭
    数字家庭
  • 群乐
    群乐
  • 产品报价 行情 经销商 渠道 评测 | 软件 设计 网页 开发 安全 论坛 E时代 游戏 图片 壁纸 下载 网摘 博客 索尼专区 Vista 科技奥运
    天极网
    Checking the system drive space threshold
    作者: Christine Polewarczyk
    出处:
    责任编辑:
    [ 2004-06-17 19:20 ]


    Here is how to check the system drive space threshold in SQL Server. (You'll need to install SQL Mail first.)

    1. Create the stored procedure (System_DriveSpace).
    2. Execute the stored procedure as a job with parameters (Drive, Threshold, Global email address/individual email address).

    1. Create the following stored procedure:

    CREATE Procedure System_DriveSpace
     (
     @DriveLetter char(1),
     @Threshold integer = null,
     @EmailTo varchar(100) = null
     )
    As
     set nocount on
     declare @FreeSpace integer
     declare @MinAllowed integer
     declare @EmailMsg varchar(200)
     declare @EmailSub varchar (200)
     declare @ServerName varchar(20)
     
     create table #tmp_DiskSize
      (drive varchar(2), 
      MB_free INT
     )
     insert into #tmp_DiskSize
     exec master..xp_fixeddrives
    
    select @FreeSpace = MB_free from #tmp_DiskSize 
    where drive = @DriveLetter
    if @Threshold is not null
     select @MinAllowed = @Threshold
     else
     select @MinAllowed = 1024
     If @FreeSpace > @MinAllowed
     Select 'Plenty of space no need for alarm'  
     Else
      begin
       select @Servername=@@ServerName
       Select 'Drive ' + @DriveLetter + ' on ' + @ServerName + ' is below minimum threshold contact dba immediately'
       if @EmailTo is not null
        begin
         select @EmailMsg = 'Drive ' + @DriveLetter + ' is below minimum threshold on ' + @ServerName + '.  Only ' + cast(@FreeSpace as varchar) + 'Mb remaining.  Escalate to DBA and  Infrastructure team immediately'
         select @EmailSub =  '**** CRITICAL CONDITION on ' + @ServerName + ' ****'    
         EXEC master.dbo.xp_sendmail @recipients = @EmailTo,
          @subject = @EmailSub,
          @message = @EmailMsg
        end
      end
     drop table #tmp_DiskSize
     return 
    

    2. Create the following job:

    -------------------------
    exec master..system_drivespace 'C', '512', 'sqlmonitors@abc.com'
    exec master..system_drivespace 'D', '1024', 'sqlmonitors@abc.com'
    exec master..system_drivespace 'E', '2048', 'sqlmonitors@abc.com'
    

    Now, schedule the job accordingly.

    笔名:
    请您注意:

     遵守国家有关法律、法规,尊重网上道德,承担一切因您的行为而直接或间接引起的法律责任。

     天极网拥有管理笔名和留言的一切权利。
    相关内容