November 15, 2011 at 1:05 pm
Comments posted to this topic are about the item Disk space alert per drive[/url]
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_DiskSpaceAlert] Script Date: 10/03/2011 15:50:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DiskSpaceAlert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_DiskSpaceAlert]
GO
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_DiskSpaceAlert] Script Date: 10/03/2011 15:50:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_DiskSpaceAlert] @MinMBFree int, @drive char(1), @RCPT VARCHAR(500) AS
/****************************************************
*Object: dbo.usp_DiskSpaceAlert (DiskSpaceAlert per diskdrive)
*Dependent Objects: master.sys.xp_fixeddrives
*Version: 1.0
*Script Date: 3/10/2011
*Author: Sven Goossens
*Purpose: Validate sufficient disk space per drive
*Detailed Description: Validate sufficient disk space based on based on the @MinMBFree and @drive parameters
*Mails when defined amount is reached to parameter @RCPT
*EXECUTE AS:
*EXEC dbo.usp_DiskSpaceAlert @MinMBFree= 30000, @drive='C', @RCPT='someone@mail.com'
*Updates:
* v1.0- Drive will be checked and sends mail when diskspace is less then given amount
****************************************************/
SET NOCOUNT ON
-- 1 - Declare variables
DECLARE @MBfree int
-- 2 - Initialize variables
SET @MBfree = 0
-- 3 - Create temp tables
CREATE TABLE #tbl_xp_fixeddrives
(Drive varchar(2) NOT NULL,
[MB free] int NOT NULL)
-- 4 - Populate #tbl_xp_fixeddrives
INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])
EXEC master.sys.xp_fixeddrives
-- 5 - Initialize the @MBfree value
SELECT @MBfree = [MB free]
FROM #tbl_xp_fixeddrives
WHERE Drive = @drive
-- 6 - Determine if sufficient fre space is available
IF @MBfree > @MinMBFree
BEGIN
RETURN
END
ELSE
BEGIN
IF CHARINDEX('@',@RCPT) > 0 --THERE IS AN @ SYMBOL IN THE RECIPIENT - SEND EMAIL
BEGIN
DECLARE @MSG VARCHAR(400)
SET @MSG = @drive + ' drive has only ' + CONVERT(VARCHAR,@MBfree) --PUT THE VARS INTO A MSG
+ 'MB (' +CONVERT(VARCHAR,@MBfree/1024)+ 'GB) left on ' + @@SERVERNAME + CHAR(13) + CHAR(10)
DECLARE @EMAIL VARCHAR(800)
SET @EMAIL = 'EXEC msdb.dbo.sp_send_dbmail
@recipients = ''' + @RCPT + ''',
@body = ''' + @MSG + ''',
@subject = ''!! LOW FREE DISK SPACE ON DRIVE ' + @drive + ' @ ' + @@SERVERNAME + ' !!'''
EXEC (@EMAIL)
END
END
-- 7 - DROP TABLE #tbl_xp_fixeddrives
DROP TABLE #tbl_xp_fixeddrives
SET NOCOUNT OFF
GO
November 24, 2011 at 10:23 pm
Difficulty to see the code of SP
November 25, 2011 at 1:13 am
Yes I know, I've tried to correct it, but it didn't helped.
I even started an topic to help me, but no solutions atm ... 🙁
December 6, 2011 at 4:06 am
Thanks for the script, hoewever it's hard to read. Perhaps include the script as an attachment to your article instead of putting it in a code block 😉
December 6, 2011 at 4:43 am
I've modified the first post to make it more readable.
Sorry for the hard reading article, but I did my best to post it good!
Hope this edit will help you all....
December 6, 2011 at 11:35 am
Thank you. I appreciate you posting your script. Might I suggest a link on configuring db mail, as this would be a prerequisite.
December 8, 2011 at 9:40 am
sp_send_dbmail needs a profile to send a message from. I added it as a param. If null then grab a profile from a system table. Also removed the duplicate DROP TABLE statement.
PS - Got those non-printable chars out of there that seem to appear in a lot of code posts. I'm told it has something to do with the editor used.
Ken
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_DiskSpaceAlert] Script Date: 12/08/2011 10:35:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DiskSpaceAlert] @MinMBFree int, @drive char(1), @RCPT VARCHAR(500), @MailProfile varchar(400) = null
AS
/*****************************************************????Object: dbo.usp_DiskSpaceAlert (DiskSpaceAlert per diskdrive)*????
Dependent Objects: master.sys.xp_fixeddrives*????
Version: 1.0*????
Script Date: 3/10/2011*????
Author: Sven Goossens*????
Purpose: Validate sufficient disk space per drive*????
Detailed Description: Validate sufficient disk space based on based on the @MinMBFree and @drive parameters*????????
Mails when defined amount is reached to parameter @RCPT*????
EXECUTE AS:*????????
EXEC dbo.usp_DiskSpaceAlert @MinMBFree= 30000, @drive='C', @RCPT='someone@mail.com'*????
Updates:*???? v1.0????- Drive will be checked and sends mail when diskspace is less then given amount****************************************************/
SET NOCOUNT ON
-- 1 - Declare variables
DECLARE @MBfree int
-- 2 - Initialize variables
SET @MBfree = 0
-- 3 - Create temp tables
CREATE TABLE #tbl_xp_fixeddrives(Drive varchar(2) NOT NULL,[MB free] int NOT NULL)
-- 4 - Populate #tbl_xp_fixeddrives
INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])
EXEC master.sys.xp_fixeddrives
-- 5 - Initialize the @MBfree value
SELECT @MBfree = [MB free]FROM #tbl_xp_fixeddrives WHERE Drive = @drive
-- 6 - Determine if sufficient free space is available
IF @MBfree > @MinMBFree
BEGIN
RETURN
END
ELSE
BEGIN
IF CHARINDEX('@',@RCPT) > 0
--THERE IS AN @ SYMBOL IN THE RECIPIENT - SEND EMAIL????
BEGIN
DECLARE @MSG VARCHAR(400)
--Get a random profile from database mail if one is not passed in
IF @MailProfile IS NULL SELECT @MailProfile = [NAME] FROM msdb..sysmail_profile
SET @MSG = @drive + ' drive has only ' + CONVERT(VARCHAR,@MBfree)
--PUT THE VARS INTO A MSG??????????
+ 'MB (' +CONVERT(VARCHAR,@MBfree/1024)+ 'GB) left on ' + @@SERVERNAME + CHAR(13) + CHAR(10)
DECLARE @EMAIL VARCHAR(600)
SET @EMAIL = 'EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''' + @MailProfile + ''',
@recipients = ''' + @RCPT + ''',
@body = ''' + @MSG + ''',
@subject = ''!! LOW FREE DISK SPACE ON DRIVE ' + @drive + ' @ ' + @@SERVERNAME + ' !!'''
EXEC (@EMAIL)
END
END
-- 7 -
DROP TABLE #tbl_xp_fixeddrives
SET NOCOUNT OFF
December 9, 2011 at 3:24 am
Thx Ken, I've used the default mail profile.
If it's not set, the errormessage will say enough to create one.
And there was no double drop table statement ... the first one was only a comment for documentation purpose in the stored procedure... 🙂
Thanks for your reply, the extra addition to add the selection of the mail profile is very usefull!
December 9, 2011 at 9:24 am
Oh, true about the DROP TABLE statement. I accidently uncommented out the comment when reformatting :laugh:
Ken
February 13, 2014 at 5:40 am
great work this.
February 19, 2014 at 10:43 am
Washout (2/13/2014)
great work this.
Thx 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply