Disk space alert script needed

• espanolanthony

  SSCarpal Tunnel

  Points: 4204

  More actions

  April 7, 2010 at 8:36 am

  #146402

  I use SQL Server 2005 environment. I was wandering if i can get a script that will email me if any of the disk on the server exceeds 80% (is 80% full). The drives on the server are C, D, E FOR DATA FILES, L FOR LOG FILES AND Z.

  so can someone send me some good scripts that will send an alert if the disk space gets full to 80%.

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  April 7, 2010 at 8:56 am

  #1147407

  Try this

  DECLARE @availableSpace AS FLOAT

  DECLARE @alertMessage AS Varchar(4000)

  CREATE TABLE #tbldiskSpace

  (

  driveName VARCHAR(3),

  freeSpace FLOAT

  )

  INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives

  SELECT @availableSpace = ROUND((freeSpace)/1024,1) FROM #tbldiskSpace WHERE driveName = 'D'

  SET @alertMessage = 'Free Space Available on D:\ Drive is' + CAST(@availableSpace AS VARCHAR) + 'GB'

  IF @availableSpace < 10

  BEGIN

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = '',

  @recipients = '',

  @body = @alertMessage,

  @importance = '',

  @subject = 'Disk Space Alert';

  END

  DROP TABLE #tbldiskSpace

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)