I have a VB script that goes out and collects all the server disk usage
on my network once an hour. It sends this information into a table.

Name,    Drive, TotalDiskSize, UsedDiskSpace, PercentageFree, DateANDTime
Server1,    C:,    69311.68750,    21694.53906,    31.29997,    2006-02-09
18:00:02.000
Server1,    F:,    558257.99609,    59010.08594,    10.57040,    2006-02-09
18:00:02.000
Server2,    C:,    34719.82422,    10734.56641,    30.91769,    2006-02-09
18:00:03.000
Server2,    F:,    345139.99609,    54710.21875,    15.85160,    2006-02-09
18:00:03.000
Server3,    C:,    69413.63281,    21412.61328,    30.84785    2006-02-09 18:00:04.000
Server3,    F:,    138841.99609,    7095.20703,    5.11027,    2006-02-09 18:00:04.000
Server4,    C:,    34719.82422,    13143.00391,    37.85447,    2006-02-09
18:00:07.000
Server4,    F:,    172569.99609,    7428.57031,    4.30467,    2006-02-09 18:00:07.000

/*****************************************
Get list of drives scaned within the last
5 Minutes
*****************************************/

Select *
from dbo.DiskMonitor_DriveSpace
where date > DATEADD(minute,-10,GetDate())

I need it to check based upon PercentageFree and then send out an alert
notifying me that the disk space is getting low e.g. 5% or less space
left. My main problem now is that I am not sure how to write a Stored
Procedure that select the information that I am looking for and then
send me an e-mail message. . Ideality I would like it to send me a
nicely formatted html file, but I will settle for plain text. Also
ideally I would like to send out a warning message prior to the
critical message at around 15%

I am on a SQL2005 system. Oh Yes I have to monitor 30+ servers

Hope that all makes sense

-Matt-

Matthew

SELECT *,
CASE WHEN PercentageFree <=5.000 THEN  'Low' ELSE 'Okay' END
FROM Table

Take look at this SP


/*
This procedure will send a notification if the free disk space on any of the
drives
SQL Server resides on is lower than the specified limit.
The alert can either be an email or netsend.

usage: exec master.dbo.sp_diskalert 'ha***@foo.com', 1000
Will send an email to ha***@foo.com if the free disk space is less than
1000mb
NB more than one email address can be specified, separate using semi colons

USAGE: EXEC master.dbo.sp_diskalert 'HARRY PARKINSON', 250
Will send the alert via net send to user harry parkinson if the free disk
space is less than 250mb
NB this could also be a computer name, normal net send rules apply

Supports sql server 7 or 2000
You need sql mail configured to send email!
If xp_cmdshell doesn't exist it will be added and dropped as needed
*/

USE master
GO

if exists
(select * from sysobjects where id = object_id(N'[dbo].[sp_diskalert]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_diskalert]
GO

create procedure sp_diskalert
@RCPT VARCHAR(500),
@LIMIT INT

AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #T1(
DRVLETTER CHAR(1),
DRVSPACE INT
)

INSERT INTO #T1 EXEC master.dbo.xp_fixeddrives

/* GENERATE THE MESSAGE */

IF (SELECT COUNT(*) FROM #T1) > 0 AND LEN(@RCPT) > 0 --CHECK THERE IS SOME
DATA AND A RECIPIENT
BEGIN
DECLARE @MSG VARCHAR(400),
  @DLETTER VARCHAR(5),
  @DSPACE INT

SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1 --GET FIRST DRIVE LETTER
   WHERE DRVSPACE < @LIMIT
   ORDER BY DRVLETTER ASC)

SET @DSPACE = (SELECT DRVSPACE FROM #T1 --GET THE DISK SPACE FOR THE LETTER
   WHERE DRVLETTER = @DLETTER)

SET @MSG =  @DLETTER + ' is at ' + CONVERT(VARCHAR,@DSPACE) --PUT THE VARS
INTO A MSG
   + 'MB' + CHAR(13) + CHAR(10)


WHILE (SELECT COUNT(*) FROM #T1 WHERE DRVSPACE < @LIMIT AND DRVLETTER >
@DLETTER) > 0
BEGIN     --LOOP THROUGH DRIVE LETTERS AND REPEAT ABOVE
  SET @DLETTER = (SELECT TOP 1 DRVLETTER FROM #T1
    WHERE DRVSPACE < @LIMIT
    AND DRVLETTER > @DLETTER
    ORDER BY DRVLETTER ASC)

  SET @DSPACE = (SELECT DRVSPACE FROM #T1
    WHERE DRVLETTER = @DLETTER)
  SET @MSG = @MSG + @DLETTER + ' is at ' + CONVERT(VARCHAR,@DSPACE) + 'MB'
    + CHAR(13) + CHAR(10)
END



/* SEND THE MESSAGE */

IF CHARINDEX('@',@RCPT) > 0  --THERE IS AN @ SYMBOL IN THE RECIPIENT - SEND
EMAIL
BEGIN
  DECLARE @EMAIL VARCHAR(600)
  SET @EMAIL = 'EXEC master.dbo.xp_sendmail
    @recipients = ''' + @RCPT + ''',
    @message = ''' + @MSG + ''',
    @subject = ''!! LOW FREE DISK SPACE ON ' + @@SERVERNAME + ' !!'''
  EXEC (@EMAIL)
END

ELSE IF CHARINDEX('@',@RCPT) = 0 --THERE IS NO @ SYMBOL IN THE RECIPIENT -
NET SEND
BEGIN
  --DETERMINE IF XP_CMDSHELL EXISTS
  DECLARE @FLAG BIT
  SET @FLAG = 1

  IF NOT EXISTS(SELECT NAME FROM master..sysobjects WHERE NAME =
'XP_CMDSHELL')
  SET @FLAG = 0

  --IF NOT RECREATE IT
  IF @FLAG = 0
  BEGIN
   EXEC sp_addextendedproc 'xp_cmdshell', 'xpsql70.dll'
   PRINT 'ADDING XP_CMDSHELL'
  END

  --NET SEND MSG
  DECLARE @NETSEND VARCHAR(600)
  SET @MSG = 'ALERT - LOW FREE DISK SPACE ON ' + @@SERVERNAME + ' : ' + @MSG
  SET @NETSEND = 'xp_cmdshell ''net send "' + RTRIM(@RCPT) + '" '
    + LEFT(RTRIM(REPLACE(@MSG,CHAR(13) + CHAR(10),', ')),LEN(@MSG)-2) + ''''
  EXEC (@NETSEND)


  IF @FLAG = 0
  BEGIN
   EXEC sp_dropextendedproc 'xp_cmdshell'
   PRINT 'DROPPING XP_CMDSHELL'
  END

END
END

DROP TABLE #T1

END
GO

Show quoteHide quote

Thanks, this is a great start, and what I was looking for. I think I
can hack my way through the code and configure it the way I want.

Matthew

Why don't you use the perfmon utility and alerts?
You could use the LogicalDisk performance object
and its % Free Space counter.
To define actions that should occur when counter
data triggers an alert, use the Action tab of the alert properties
dialog box.
---
Andrey Odegov
avode***@yandex.ru
(remove GOV to respond)

Matthew wrote:
Show quoteHide quoteHave you considered using Perfmon? Or if you need something more
sophisticated:
http://www.microsoft.com/mom/default.mspx

Query help - resultset too large... impossible!
Problem updating two tables in a transaction.
Does dynamic SQL allow table variables?
DBCC SHOWCONTIG question
Query assistance or advice
Insert Trigger
Any way to avoid using a cursor and a script on this one?
Problem with Cursor and Union in select
Optimizing query with UDF and table vars and IN
SELECT problem in stored procedure