September 12, 2011 at 3:04 pm
Hello all,
I was not sure where to post this question at. Thats why i started here. I have a sql server installed on one of the server but now we want to monitor free and used space for each logical Disk( Drive letter C:, D:, E: etc etc).
Just like when we open up my computer it shows which drive have how much free and how much used.
I found this article ... which seems like it talks about monitoring it...but i would like to receive alert as an email about it ??? which the below article seems not to do that...
http://support.microsoft.com/kb/324796
or if you guys can point me to a simple script that i can deploy on the server to send myself an alert as to how much is free and how much is used on each drive....
Thanks and all your help is much appericiate....
September 12, 2011 at 3:19 pm
This is kind of crude code but works well and will email you every few minutes depending on how you schedule the Agent job. Create a job with this TSQL in it:
-- WARNING #2 - CRITICAL WARNING Sends notification every 5 minutes for for 2 hours
DECLARE @Success int, @Count INT, @Threshold int
SET @Count = 0
SET @Threshold = 4096
EXEC @Success = dbo.utl_dba_MonitorDiskSize 'dba_alerts@yourcompany.com', @Threshold
WHILE (@Count < 25 AND @Success = 1)
BEGIN
WAITFOR DELAY '00:05:00'
EXEC @Success = dbo.utl_dba_MonitorDiskSize 'dba_alerts@yourcompany.com', @Threshold
SET @Count = @Count + 1
END
Then use something like this:
USE [F1Settings]
GO
/****** Object: StoredProcedure [dbo].[utl_dba_MonitorDiskSize] Script Date: 09/12/2011 16:17:55 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[utl_dba_MonitorDiskSize]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[utl_dba_MonitorDiskSize]
GO
USE [F1Settings]
GO
/****** Object: StoredProcedure [dbo].[utl_dba_MonitorDiskSize] Script Date: 09/12/2011 16:17:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* ############################################################################################################### */
--MONITOR DISK SIZE
/* ############################################################################################################### */
CREATE procedure [dbo].[utl_dba_MonitorDiskSize]
@RCPT VARCHAR(500),
@LIMIT INT,
@Success int = NULL OUTPUT
AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Checks the filesystem and reports the size of the drives
Department:DBA
Created For:
----------------------------------------------------------------------------------------------------------------
NOTES:@LIMIT - is the set threshold that triggers and email when reached
----------------------------------------------------------------------------------------------------------------
Created On:03/14/2011
Created By:MyDoggieJessie----------------------------------------------------------------------------------------------------------------
Modified On:
Modified By:
Changes:
----------------------------------------------------------------------------------------------------------------
exec utl_dba_MonitorDiskSize you@yourcompany.com', 30000
*/
BEGIN
SET NOCOUNT ON
/* ######################################### START MAIN PROCEDURE HERE ########################################## */
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
DELETE FROM #T1 WHERE DRVLETTER = 'C'
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 VARSINTO 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)
SET @DSPACE = (
SELECT DRVSPACE FROM #T1
WHERE DRVLETTER = @DLETTER)
SET @MSG = @MSG + @DLETTER + ' is at ' + CONVERT(VARCHAR,@DSPACE) + 'MB'
+ CHAR(13) + CHAR(10)
END
IF (@DSPACE < @LIMIT)
SELECT @Success = 1
ELSE
SELECT @Success = 0
/* SEND THE MESSAGE */
IF CHARINDEX('@',@RCPT) > 0 --THERE IS AN @ SYMBOL IN THE RECIPIENT - SENDEMAIL
BEGIN
DECLARE @EMAIL VARCHAR(600)
SET @EMAIL = 'EXEC msdb.dbo.sp_send_dbmail
@recipients = ''' + @RCPT + ''',
@Body = ''' + @MSG + ''',
@sensitivity = ''private'',
@subject = ''!! LOW FREE DISK SPACE ON ' + @@SERVERNAME + ' !!'''
EXEC (@EMAIL)
END
END
/* ########################################## END MAIN PROCEDURE HERE ########################################### */
DROP TABLE #T1
IF @Success = 1
RETURN 1
ELSE
RETURN 0
END
/*
exec utl_dba_MonitorDiskSize you@yourcompany.com', 30000
*/
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 14, 2011 at 7:36 am
Thanks for the info,
I was looking for something more at OS level than inside the instance ?
September 14, 2011 at 5:12 pm
?
It will accomplish exactly what you're asking for:
1. send yourself an alert as to how much is free and (how much is used on each drive - Max space - free) for each logical Disk( Drive letter C:, D:, E: etc etc).
If you don't want to use SQL server, google a windows batch script/VBScript and use the Task Scheduler to have it run according to a schedule you deem fit
If you want to relay on SQL server, I use the script provided across our entire enviroment - it works for us
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
September 14, 2011 at 5:16 pm
You could also check for a powershell script to do that stuff if you don't want to use SQL.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply