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
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:[vbcol=seagreen]
> 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...
|||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...
>
>
2012年3月27日星期二
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:[vbcol=seagreen]
> 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...|||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 t
he
> 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 colon
s
> 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_diskale
rt]')
> 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 LETT
ER
> WHERE DRVLETTER = @.DLETTER)
> SET @.MSG = @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) --PUT THE VAR
S
> 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 - SE
ND
> 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 + ' : ' + @.M
SG
> 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.co
m>
> wrote in message news:C443C2B6-E17E-48DD-8657-BD20C6235E7B@.microsoft.com..
.
>
>
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
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:[vbcol=seagreen]
> 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...|||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 t
he
> 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 colon
s
> 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_diskale
rt]')
> 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 LETT
ER
> WHERE DRVLETTER = @.DLETTER)
> SET @.MSG = @.DLETTER + ' is at ' + CONVERT(VARCHAR,@.DSPACE) --PUT THE VAR
S
> 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 - SE
ND
> 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 + ' : ' + @.M
SG
> 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.co
m>
> wrote in message news:C443C2B6-E17E-48DD-8657-BD20C6235E7B@.microsoft.com..
.
>
>
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
>
>
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
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
> 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
>
>
Disk Space Alert
Ok, here is my question... Is there a SQL Server alert that would notify in case the server is running out of disk space?
I've only found messages that notify if the database cannot be restored or backed up because of insufficient disk space. However, I need to know if server is running out disk space in general.
I've also looked at the windows performance counters, but with my limited knowledge of them, I couldn't find what I need.
Any suggestions would be greatly appreciated :-)Try this page using xp_fixeddrives
http://www.databasejournal.com/features/mssql/article.php/3080501
HTH|||Thank you, Ray!!!! :-)
Originally posted by rhigdon
Try this page using xp_fixeddrives
http://www.databasejournal.com/features/mssql/article.php/3080501
HTH
I've only found messages that notify if the database cannot be restored or backed up because of insufficient disk space. However, I need to know if server is running out disk space in general.
I've also looked at the windows performance counters, but with my limited knowledge of them, I couldn't find what I need.
Any suggestions would be greatly appreciated :-)Try this page using xp_fixeddrives
http://www.databasejournal.com/features/mssql/article.php/3080501
HTH|||Thank you, Ray!!!! :-)
Originally posted by rhigdon
Try this page using xp_fixeddrives
http://www.databasejournal.com/features/mssql/article.php/3080501
HTH
Disk space
Hi does anyone know what I can do to alert me when the
disk space starts to run out
thanks for any help MikeySee reply in .programming
John
"Mikey" <anonymous@.discussions.microsoft.com> wrote in message
news:140f801c3f7f8$e81ed270$a401280a@.phx
.gbl...
> Hi does anyone know what I can do to alert me when the
> disk space starts to run out
> thanks for any help Mikey|||This works for me.
checkdrives.vbs run in task schededuler every hour
'***********************************
'Monitors Drive Space and emails if below threshhold
'Application Variables
'Message tagged to the bottom of each email
Dim sEmailBlurb
sEmailBlurb = " " & VbCrLf & VbCrLf & VbCrLf & "WARNING - This email and any
attachments may be confidential. If received in error, please delete and
inform us by return email." & VbCrLf & VbCrLf &_
"Because emails and attachments may be interfered with, may contain computer
viruses or other defects and may not be successfully replicated on other
systems, you must be cautious." & VbCrLf &_
"CokeShop cannot guarantee that what you receive is what we sent. If you
have any doubts about the authenticity of an email by CokeShop, please
contact us immediately. " & VbCrLf &_
"It is also important to check for viruses and defects before opening or
using attachments. CokeShop's liability is limited to resupplying any
affected attachments." & VbCrLf & VbCrLf &_
"CokeShop Online ABN is xx xxx xxx xxx."
On error resume next
'Application Variables
Dim sEmailFrom, sEmailTo, sMyMailServer, sDriveC, sDriveD, sBodyText,
sSubject
sEmailFrom = "xxx@.assoft.com.au"
sEmailTo = "yyy@.assoft.com.au"
sConEmailMonitor = "zzz@.assoft.com.au"
sMyMailServer = "assoftsvr"
iEmailPort = 25
'Does drive C: or D: have less than 3 gigabyte free if so email support
sDriveC = RetDriveSpace("c:\")
sDriveD = RetDriveSpace("d:\")
If Trim(sDriveC & sDriveD) <> "" Then
sBodyText = "CokeShop Hard Drive Storage, is getting low on space" & vbcrlf
& _
"Please rectify this problem Urgently" & VbCrLf & VbCrLf & _
"Drive C: =" & sDriveC & " Drive D: =" & sDriveD & VbCrLf &
VbCrLf & _
"Automated email from CokeShop System " & VbCrLf &
FormatDateTime(Now,1)
sSubject = "CokeShop Drive space low !!!"
SendEmail sSubject,sBodyText
End If
Function RetDriveSpace(drvpath)
Dim fso, d, s
Set fso = CreateObject("Scripting.FileSystemObject")
Set d = fso.GetDrive(fso.GetDriveName(fso.GetAbsolutePathName(drvpath)))
If FormatNumber(d.AvailableSpace/(1024 * 1000000), 1) < 3 Then
s = FormatNumber(d.AvailableSpace/(1024 * 1000000), 1)
End If
RetDriveSpace = s
Set fso = Nothing
Set d = Nothing
End Function
Function SendEmail(sSubJect, sBody)
'***************************************
***************
'*** Send the message Using CDOSYS Win2k & Win2003 ****
'***************************************
***************
' CDO mail object
sch = "http://schemas.microsoft.com/cdo/configuration/"
Set cdoConfig = CreateObject("CDO.Configuration")
cdoConfig.Fields.Item(sch & "sendusing") = 2
cdoConfig.Fields.Item(sch & "smtpserverport") = iEmailPort
cdoConfig.Fields.Item(sch & "smtpserver") = sMyMailServer
cdoConfig.fields.update
Set cdoMessage = CreateObject("CDO.Message")
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = sEmailFrom
cdoMessage.To = sEmailTo
cdoMessage.BCC = sConEmailMonitor
cdoMessage.Subject = sSubJect
cdoMessage.TextBody = sBody & sEmailBlurb
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti
cate").value = 1 ' use clear text authenticate
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendpassword
").value ="mypassword"
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendusername
").value ="yourusername"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-MSMail-Priority") =
"High"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Priority") = 2
cdoMessage.Fields.Item("urn:schemas:mailheader:Keywords") = "COKESHOP"
cdoMessage.Fields.Item("urn:schemas:mailheader:Sensitivity") =
"Company-Confidential"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Message-Flag") = "Do
not Forward"
cdoMessage.Fields.Update
cdoMessage.Send
Set cdoMessage = Nothing
Set cdoConfig = Nothing
End Function
'***************************************
******
"Mikey" <anonymous@.discussions.microsoft.com> wrote in message
news:140f801c3f7f8$e81ed270$a401280a@.phx
.gbl...
> Hi does anyone know what I can do to alert me when the
> disk space starts to run out
> thanks for any help Mikeysql
disk space starts to run out
thanks for any help MikeySee reply in .programming
John
"Mikey" <anonymous@.discussions.microsoft.com> wrote in message
news:140f801c3f7f8$e81ed270$a401280a@.phx
.gbl...
> Hi does anyone know what I can do to alert me when the
> disk space starts to run out
> thanks for any help Mikey|||This works for me.
checkdrives.vbs run in task schededuler every hour
'***********************************
'Monitors Drive Space and emails if below threshhold
'Application Variables
'Message tagged to the bottom of each email
Dim sEmailBlurb
sEmailBlurb = " " & VbCrLf & VbCrLf & VbCrLf & "WARNING - This email and any
attachments may be confidential. If received in error, please delete and
inform us by return email." & VbCrLf & VbCrLf &_
"Because emails and attachments may be interfered with, may contain computer
viruses or other defects and may not be successfully replicated on other
systems, you must be cautious." & VbCrLf &_
"CokeShop cannot guarantee that what you receive is what we sent. If you
have any doubts about the authenticity of an email by CokeShop, please
contact us immediately. " & VbCrLf &_
"It is also important to check for viruses and defects before opening or
using attachments. CokeShop's liability is limited to resupplying any
affected attachments." & VbCrLf & VbCrLf &_
"CokeShop Online ABN is xx xxx xxx xxx."
On error resume next
'Application Variables
Dim sEmailFrom, sEmailTo, sMyMailServer, sDriveC, sDriveD, sBodyText,
sSubject
sEmailFrom = "xxx@.assoft.com.au"
sEmailTo = "yyy@.assoft.com.au"
sConEmailMonitor = "zzz@.assoft.com.au"
sMyMailServer = "assoftsvr"
iEmailPort = 25
'Does drive C: or D: have less than 3 gigabyte free if so email support
sDriveC = RetDriveSpace("c:\")
sDriveD = RetDriveSpace("d:\")
If Trim(sDriveC & sDriveD) <> "" Then
sBodyText = "CokeShop Hard Drive Storage, is getting low on space" & vbcrlf
& _
"Please rectify this problem Urgently" & VbCrLf & VbCrLf & _
"Drive C: =" & sDriveC & " Drive D: =" & sDriveD & VbCrLf &
VbCrLf & _
"Automated email from CokeShop System " & VbCrLf &
FormatDateTime(Now,1)
sSubject = "CokeShop Drive space low !!!"
SendEmail sSubject,sBodyText
End If
Function RetDriveSpace(drvpath)
Dim fso, d, s
Set fso = CreateObject("Scripting.FileSystemObject")
Set d = fso.GetDrive(fso.GetDriveName(fso.GetAbsolutePathName(drvpath)))
If FormatNumber(d.AvailableSpace/(1024 * 1000000), 1) < 3 Then
s = FormatNumber(d.AvailableSpace/(1024 * 1000000), 1)
End If
RetDriveSpace = s
Set fso = Nothing
Set d = Nothing
End Function
Function SendEmail(sSubJect, sBody)
'***************************************
***************
'*** Send the message Using CDOSYS Win2k & Win2003 ****
'***************************************
***************
' CDO mail object
sch = "http://schemas.microsoft.com/cdo/configuration/"
Set cdoConfig = CreateObject("CDO.Configuration")
cdoConfig.Fields.Item(sch & "sendusing") = 2
cdoConfig.Fields.Item(sch & "smtpserverport") = iEmailPort
cdoConfig.Fields.Item(sch & "smtpserver") = sMyMailServer
cdoConfig.fields.update
Set cdoMessage = CreateObject("CDO.Message")
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = sEmailFrom
cdoMessage.To = sEmailTo
cdoMessage.BCC = sConEmailMonitor
cdoMessage.Subject = sSubJect
cdoMessage.TextBody = sBody & sEmailBlurb
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti
cate").value = 1 ' use clear text authenticate
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendpassword
").value ="mypassword"
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendusername
").value ="yourusername"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-MSMail-Priority") =
"High"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Priority") = 2
cdoMessage.Fields.Item("urn:schemas:mailheader:Keywords") = "COKESHOP"
cdoMessage.Fields.Item("urn:schemas:mailheader:Sensitivity") =
"Company-Confidential"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Message-Flag") = "Do
not Forward"
cdoMessage.Fields.Update
cdoMessage.Send
Set cdoMessage = Nothing
Set cdoConfig = Nothing
End Function
'***************************************
******
"Mikey" <anonymous@.discussions.microsoft.com> wrote in message
news:140f801c3f7f8$e81ed270$a401280a@.phx
.gbl...
> Hi does anyone know what I can do to alert me when the
> disk space starts to run out
> thanks for any help Mikeysql
Disk space
Hi does anyone know what I can do to alert me when the
disk space starts to run out
thanks for any help MikeyDoesn't your hardware manuafacturer have an snmp probe for
you to manage this?
Example - Dell has Open Manage and Compaq has CIM
Paul
>--Original Message--
>Hi does anyone know what I can do to alert me when the
>disk space starts to run out
>thanks for any help Mikey
>.
>|||See reply in .programming
John
"Mikey" <anonymous@.discussions.microsoft.com> wrote in message
news:140f801c3f7f8$e81ed270$a401280a@.phx.gbl...
> Hi does anyone know what I can do to alert me when the
> disk space starts to run out
> thanks for any help Mikey|||This works for me.
checkdrives.vbs run in task schededuler every hour
'***********************************
'Monitors Drive Space and emails if below threshhold
'Application Variables
'Message tagged to the bottom of each email
Dim sEmailBlurb
sEmailBlurb = " " & VbCrLf & VbCrLf & VbCrLf & "WARNING - This email and any
attachments may be confidential. If received in error, please delete and
inform us by return email." & VbCrLf & VbCrLf &_
"Because emails and attachments may be interfered with, may contain computer
viruses or other defects and may not be successfully replicated on other
systems, you must be cautious." & VbCrLf &_
"CokeShop cannot guarantee that what you receive is what we sent. If you
have any doubts about the authenticity of an email by CokeShop, please
contact us immediately. " & VbCrLf &_
"It is also important to check for viruses and defects before opening or
using attachments. CokeShop's liability is limited to resupplying any
affected attachments." & VbCrLf & VbCrLf &_
"CokeShop Online ABN is xx xxx xxx xxx."
On error resume next
'Application Variables
Dim sEmailFrom, sEmailTo, sMyMailServer, sDriveC, sDriveD, sBodyText,
sSubject
sEmailFrom = "xxx@.assoft.com.au"
sEmailTo = "yyy@.assoft.com.au"
sConEmailMonitor = "zzz@.assoft.com.au"
sMyMailServer = "assoftsvr"
iEmailPort = 25
'Does drive C: or D: have less than 3 gigabyte free if so email support
sDriveC = RetDriveSpace("c:\")
sDriveD = RetDriveSpace("d:\")
If Trim(sDriveC & sDriveD) <> "" Then
sBodyText = "CokeShop Hard Drive Storage, is getting low on space" & vbcrlf
& _
"Please rectify this problem Urgently" & VbCrLf & VbCrLf & _
"Drive C: =" & sDriveC & " Drive D: =" & sDriveD & VbCrLf &
VbCrLf & _
"Automated email from CokeShop System " & VbCrLf &
FormatDateTime(Now,1)
sSubject = "CokeShop Drive space low !!!"
SendEmail sSubject,sBodyText
End If
Function RetDriveSpace(drvpath)
Dim fso, d, s
Set fso = CreateObject("Scripting.FileSystemObject")
Set d = fso.GetDrive(fso.GetDriveName(fso.GetAbsolutePathName(drvpath)))
If FormatNumber(d.AvailableSpace/(1024 * 1000000), 1) < 3 Then
s = FormatNumber(d.AvailableSpace/(1024 * 1000000), 1)
End If
RetDriveSpace = s
Set fso = Nothing
Set d = Nothing
End Function
Function SendEmail(sSubJect, sBody)
'******************************************************
'*** Send the message Using CDOSYS Win2k & Win2003 ****
'******************************************************
' CDO mail object
sch = "http://schemas.microsoft.com/cdo/configuration/"
Set cdoConfig = CreateObject("CDO.Configuration")
cdoConfig.Fields.Item(sch & "sendusing") = 2
cdoConfig.Fields.Item(sch & "smtpserverport") = iEmailPort
cdoConfig.Fields.Item(sch & "smtpserver") = sMyMailServer
cdoConfig.fields.update
Set cdoMessage = CreateObject("CDO.Message")
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = sEmailFrom
cdoMessage.To = sEmailTo
cdoMessage.BCC = sConEmailMonitor
cdoMessage.Subject = sSubJect
cdoMessage.TextBody = sBody & sEmailBlurb
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti
cate").value = 1 ' use clear text authenticate
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendpassword
").value ="mypassword"
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendusername
").value ="yourusername"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-MSMail-Priority") ="High"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Priority") = 2
cdoMessage.Fields.Item("urn:schemas:mailheader:Keywords") = "COKESHOP"
cdoMessage.Fields.Item("urn:schemas:mailheader:Sensitivity") ="Company-Confidential"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Message-Flag") = "Do
not Forward"
cdoMessage.Fields.Update
cdoMessage.Send
Set cdoMessage = Nothing
Set cdoConfig = Nothing
End Function
'*********************************************
"Mikey" <anonymous@.discussions.microsoft.com> wrote in message
news:140f801c3f7f8$e81ed270$a401280a@.phx.gbl...
> Hi does anyone know what I can do to alert me when the
> disk space starts to run out
> thanks for any help Mikey
disk space starts to run out
thanks for any help MikeyDoesn't your hardware manuafacturer have an snmp probe for
you to manage this?
Example - Dell has Open Manage and Compaq has CIM
Paul
>--Original Message--
>Hi does anyone know what I can do to alert me when the
>disk space starts to run out
>thanks for any help Mikey
>.
>|||See reply in .programming
John
"Mikey" <anonymous@.discussions.microsoft.com> wrote in message
news:140f801c3f7f8$e81ed270$a401280a@.phx.gbl...
> Hi does anyone know what I can do to alert me when the
> disk space starts to run out
> thanks for any help Mikey|||This works for me.
checkdrives.vbs run in task schededuler every hour
'***********************************
'Monitors Drive Space and emails if below threshhold
'Application Variables
'Message tagged to the bottom of each email
Dim sEmailBlurb
sEmailBlurb = " " & VbCrLf & VbCrLf & VbCrLf & "WARNING - This email and any
attachments may be confidential. If received in error, please delete and
inform us by return email." & VbCrLf & VbCrLf &_
"Because emails and attachments may be interfered with, may contain computer
viruses or other defects and may not be successfully replicated on other
systems, you must be cautious." & VbCrLf &_
"CokeShop cannot guarantee that what you receive is what we sent. If you
have any doubts about the authenticity of an email by CokeShop, please
contact us immediately. " & VbCrLf &_
"It is also important to check for viruses and defects before opening or
using attachments. CokeShop's liability is limited to resupplying any
affected attachments." & VbCrLf & VbCrLf &_
"CokeShop Online ABN is xx xxx xxx xxx."
On error resume next
'Application Variables
Dim sEmailFrom, sEmailTo, sMyMailServer, sDriveC, sDriveD, sBodyText,
sSubject
sEmailFrom = "xxx@.assoft.com.au"
sEmailTo = "yyy@.assoft.com.au"
sConEmailMonitor = "zzz@.assoft.com.au"
sMyMailServer = "assoftsvr"
iEmailPort = 25
'Does drive C: or D: have less than 3 gigabyte free if so email support
sDriveC = RetDriveSpace("c:\")
sDriveD = RetDriveSpace("d:\")
If Trim(sDriveC & sDriveD) <> "" Then
sBodyText = "CokeShop Hard Drive Storage, is getting low on space" & vbcrlf
& _
"Please rectify this problem Urgently" & VbCrLf & VbCrLf & _
"Drive C: =" & sDriveC & " Drive D: =" & sDriveD & VbCrLf &
VbCrLf & _
"Automated email from CokeShop System " & VbCrLf &
FormatDateTime(Now,1)
sSubject = "CokeShop Drive space low !!!"
SendEmail sSubject,sBodyText
End If
Function RetDriveSpace(drvpath)
Dim fso, d, s
Set fso = CreateObject("Scripting.FileSystemObject")
Set d = fso.GetDrive(fso.GetDriveName(fso.GetAbsolutePathName(drvpath)))
If FormatNumber(d.AvailableSpace/(1024 * 1000000), 1) < 3 Then
s = FormatNumber(d.AvailableSpace/(1024 * 1000000), 1)
End If
RetDriveSpace = s
Set fso = Nothing
Set d = Nothing
End Function
Function SendEmail(sSubJect, sBody)
'******************************************************
'*** Send the message Using CDOSYS Win2k & Win2003 ****
'******************************************************
' CDO mail object
sch = "http://schemas.microsoft.com/cdo/configuration/"
Set cdoConfig = CreateObject("CDO.Configuration")
cdoConfig.Fields.Item(sch & "sendusing") = 2
cdoConfig.Fields.Item(sch & "smtpserverport") = iEmailPort
cdoConfig.Fields.Item(sch & "smtpserver") = sMyMailServer
cdoConfig.fields.update
Set cdoMessage = CreateObject("CDO.Message")
Set cdoMessage.Configuration = cdoConfig
cdoMessage.From = sEmailFrom
cdoMessage.To = sEmailTo
cdoMessage.BCC = sConEmailMonitor
cdoMessage.Subject = sSubJect
cdoMessage.TextBody = sBody & sEmailBlurb
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti
cate").value = 1 ' use clear text authenticate
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendpassword
").value ="mypassword"
'
cdoMessage.item("http://schemas.microsoft.com/cdo/configuration/sendusername
").value ="yourusername"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-MSMail-Priority") ="High"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Priority") = 2
cdoMessage.Fields.Item("urn:schemas:mailheader:Keywords") = "COKESHOP"
cdoMessage.Fields.Item("urn:schemas:mailheader:Sensitivity") ="Company-Confidential"
cdoMessage.Fields.Item("urn:schemas:mailheader:X-Message-Flag") = "Do
not Forward"
cdoMessage.Fields.Update
cdoMessage.Send
Set cdoMessage = Nothing
Set cdoConfig = Nothing
End Function
'*********************************************
"Mikey" <anonymous@.discussions.microsoft.com> wrote in message
news:140f801c3f7f8$e81ed270$a401280a@.phx.gbl...
> Hi does anyone know what I can do to alert me when the
> disk space starts to run out
> thanks for any help Mikey
2012年3月22日星期四
Disk Alert (please help!)
Hi,
I want to setup an alert to let me know when my disk reaches 80% capacity
(20% space available).
I did the following :
1) Set the diskperf-ym switch and restarted the server.
2) Added the logical disk - disk free space counter in the perfmon
3) Set the alert value to over 80%
4) Sample data every 15 minutes.
My question is, how can i setup sql server to generate an email and send it
to the operators i create. I know that i can create custom alerts, but will
they read it from the windows event log for the above-mentioned
process..Kinda confused...please helpppp!!
Regards,
AndyHi Vishal,
Thank you for the link, however, my question still is , whether the
procedure, as per my previous mail is something that can be done ? will a
sql alert be able to read the windows application log and send email...if
that is the case then, i can do it the way i initially wanted to.
Thanks,
Andy
"Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
news:udXP2EKRDHA.1556@.TK2MSFTNGP10.phx.gbl...
> Refer to this url
> http://www.databasejournal.com/scripts/article.php/1470811
> --
> -Vishal
> "Andy" <andy_rob108@.hotmail.com> wrote in message
> news:eACZ0$JRDHA.3768@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I want to setup an alert to let me know when my disk reaches 80%
capacity
> > (20% space available).
> >
> > I did the following :
> >
> > 1) Set the diskperf-ym switch and restarted the server.
> > 2) Added the logical disk - disk free space counter in the perfmon
> > 3) Set the alert value to over 80%
> > 4) Sample data every 15 minutes.
> >
> > My question is, how can i setup sql server to generate an email and send
> it
> > to the operators i create. I know that i can create custom alerts, but
> will
> > they read it from the windows event log for the above-mentioned
> > process..Kinda confused...please helpppp!!
> >
> > Regards,
> > Andy
> >
> >
>|||Hi Andy,
I think your intended idea is not difficult to implement. Try this:
create a batch file (c:\spacealert.bat) that looks like:
osql -Sservername -Ppassword -Usa -Q"exec master..xp_sendmail @.recipients =operator, @.subject = 'disk space has exceeded limit'"
In the alert configuration window, on Action tab, click on "Run this
program" and browse to c:\spacealert.bat. This tells the alert when firing,
will trigger the batch file to execute xp_sendmail to email message to the
operators.
Richard
"Andy" <andy_rob108@.hotmail.com> wrote in message
news:eACZ0$JRDHA.3768@.tk2msftngp13.phx.gbl...
> Hi,
> I want to setup an alert to let me know when my disk reaches 80% capacity
> (20% space available).
> I did the following :
> 1) Set the diskperf-ym switch and restarted the server.
> 2) Added the logical disk - disk free space counter in the perfmon
> 3) Set the alert value to over 80%
> 4) Sample data every 15 minutes.
> My question is, how can i setup sql server to generate an email and send
it
> to the operators i create. I know that i can create custom alerts, but
will
> they read it from the windows event log for the above-mentioned
> process..Kinda confused...please helpppp!!
> Regards,
> Andy
>
I want to setup an alert to let me know when my disk reaches 80% capacity
(20% space available).
I did the following :
1) Set the diskperf-ym switch and restarted the server.
2) Added the logical disk - disk free space counter in the perfmon
3) Set the alert value to over 80%
4) Sample data every 15 minutes.
My question is, how can i setup sql server to generate an email and send it
to the operators i create. I know that i can create custom alerts, but will
they read it from the windows event log for the above-mentioned
process..Kinda confused...please helpppp!!
Regards,
AndyHi Vishal,
Thank you for the link, however, my question still is , whether the
procedure, as per my previous mail is something that can be done ? will a
sql alert be able to read the windows application log and send email...if
that is the case then, i can do it the way i initially wanted to.
Thanks,
Andy
"Vishal Parkar" <vgparkar@.hotmail.com> wrote in message
news:udXP2EKRDHA.1556@.TK2MSFTNGP10.phx.gbl...
> Refer to this url
> http://www.databasejournal.com/scripts/article.php/1470811
> --
> -Vishal
> "Andy" <andy_rob108@.hotmail.com> wrote in message
> news:eACZ0$JRDHA.3768@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I want to setup an alert to let me know when my disk reaches 80%
capacity
> > (20% space available).
> >
> > I did the following :
> >
> > 1) Set the diskperf-ym switch and restarted the server.
> > 2) Added the logical disk - disk free space counter in the perfmon
> > 3) Set the alert value to over 80%
> > 4) Sample data every 15 minutes.
> >
> > My question is, how can i setup sql server to generate an email and send
> it
> > to the operators i create. I know that i can create custom alerts, but
> will
> > they read it from the windows event log for the above-mentioned
> > process..Kinda confused...please helpppp!!
> >
> > Regards,
> > Andy
> >
> >
>|||Hi Andy,
I think your intended idea is not difficult to implement. Try this:
create a batch file (c:\spacealert.bat) that looks like:
osql -Sservername -Ppassword -Usa -Q"exec master..xp_sendmail @.recipients =operator, @.subject = 'disk space has exceeded limit'"
In the alert configuration window, on Action tab, click on "Run this
program" and browse to c:\spacealert.bat. This tells the alert when firing,
will trigger the batch file to execute xp_sendmail to email message to the
operators.
Richard
"Andy" <andy_rob108@.hotmail.com> wrote in message
news:eACZ0$JRDHA.3768@.tk2msftngp13.phx.gbl...
> Hi,
> I want to setup an alert to let me know when my disk reaches 80% capacity
> (20% space available).
> I did the following :
> 1) Set the diskperf-ym switch and restarted the server.
> 2) Added the logical disk - disk free space counter in the perfmon
> 3) Set the alert value to over 80%
> 4) Sample data every 15 minutes.
> My question is, how can i setup sql server to generate an email and send
it
> to the operators i create. I know that i can create custom alerts, but
will
> they read it from the windows event log for the above-mentioned
> process..Kinda confused...please helpppp!!
> Regards,
> Andy
>
订阅：
博文 (Atom)