disk space full alert

How do I setup alert for the disk space full warning?
For example, I want to throw an alert when 80% of c:\ is used ... so that
some clean up operation can happen ...
Please give detailed answer to this ...
regards
KPHi
This one i have just found on internet
/*
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 'harry@.foo.com', 1000
Will send an email to harry@.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
"Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
wrote in message news:C443C2B6-E17E-48DD-8657-BD20C6235E7B@.microsoft.com...
> How do I setup alert for the disk space full warning?
> For example, I want to throw an alert when 80% of c:\ is used ... so that
> some clean up operation can happen ...
> Please give detailed answer to this ...
> regards
> KP|||Even though Uri's solution will work, it might be better to look for a
monitoring tool that can monitor your diskusage, free space etc. rather than
trying to tweak SQL server to do it. I'd also think that you have the risk
that if the drive run out of space, SQL server stops and then you'll never
get the email.
Regards
Steen
Uri Dimant wrote:
> Hi
> This one i have just found on internet
> /*
> 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 'harry@.foo.com', 1000
> Will send an email to harry@.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
> "Krishnaprasad Paralikar"
> <KrishnaprasadParalikar@.discussions.microsoft.com> wrote in message
> news:C443C2B6-E17E-48DD-8657-BD20C6235E7B@.microsoft.com...
>> How do I setup alert for the disk space full warning?
>> For example, I want to throw an alert when 80% of c:\ is used ...
>> so that some clean up operation can happen ...
>> Please give detailed answer to this ...
>> regards
>> KP|||Krishnaprasad,
If you have the budget, I would recommend evaluating a few enterprise
monitoring solutions as they will tend to be more robust, and
configurable. However, they tend to be very expensive.
If you want something quick and dirty, you can either write a VBScript
and use the FileSystemObject to check drives on a server - parameterise
this so you can pass in a server name, and possibly even a drive letter.
Also, take a look at xp_fixeddrives stored procedure for a T-SQL based
solution. You could poll this every day in a SQLAgent job, and log to a
table so you can plot a trend.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Krishnaprasad Paralikar wrote:
> How do I setup alert for the disk space full warning?
> For example, I want to throw an alert when 80% of c:\ is used ... so that
> some clean up operation can happen ...
> Please give detailed answer to this ...
> regards
> KP|||Hi Uri,
Thanx a lot for the solution. It works for me on my local machine. I get the
mail in my mailbox.
Now when I exec the same proc on a different SQL server on the same network,
giving same parameters, it fails for xp_sendmail.
I get the error - xp_sendmail: Procedure expects parameter @.user, which was
not supplied.
I serached using BOL, xp_sendmail does not take @.user parameter, it takes
@.Set_user. But setting this parameter does not solve the problem.
What is wong in this? Please advice.
regards
KP
"Uri Dimant" wrote:
> Hi
> This one i have just found on internet
> /*
> 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 'harry@.foo.com', 1000
> Will send an email to harry@.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
> "Krishnaprasad Paralikar" <KrishnaprasadParalikar@.discussions.microsoft.com>
> wrote in message news:C443C2B6-E17E-48DD-8657-BD20C6235E7B@.microsoft.com...
> > How do I setup alert for the disk space full warning?
> >
> > For example, I want to throw an alert when 80% of c:\ is used ... so that
> > some clean up operation can happen ...
> >
> > Please give detailed answer to this ...
> >
> > regards
> > KP
>
>