Disks

I need additional disk space for storing my backups on SQL server 2000. Any
ideas on the latest and greatest discs I can purchase. Thanks
What disks you can buy probably depends on your hardware. Are you running
IDE? SATA? SCSI? Buy whatever disks work with your current hardware,
provide enough storage, and are cost effective for your environment. Don't
forget about growth...you probably don't want to buy drives in another
month or two.
Keith
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:8A8A8D80-7E98-43DA-B78C-3EC3232DB8FF@.microsoft.com...
> I need additional disk space for storing my backups on SQL server 2000.
Any
> ideas on the latest and greatest discs I can purchase. Thanks
|||Any limits that sql server has as far as how many discs you can add? I still
have 4 slots open. We have SCSI discs some are 72.8 GB others are 36.4GB
Thanks
"Keith Kratochvil" wrote:

> What disks you can buy probably depends on your hardware. Are you running
> IDE? SATA? SCSI? Buy whatever disks work with your current hardware,
> provide enough storage, and are cost effective for your environment. Don't
> forget about growth...you probably don't want to buy drives in another
> month or two.
> --
> Keith
>
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:8A8A8D80-7E98-43DA-B78C-3EC3232DB8FF@.microsoft.com...
> Any
>
|||SQL Server will use what the OS provides.
Keith
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:0044BB2C-7897-4DA6-9237-AED43D7F35AE@.microsoft.com...
> Any limits that sql server has as far as how many discs you can add? I
still[vbcol=seagreen]
> have 4 slots open. We have SCSI discs some are 72.8 GB others are 36.4GB
> Thanks
> "Keith Kratochvil" wrote:
running[vbcol=seagreen]
Don't[vbcol=seagreen]
2000.[vbcol=seagreen]

Disks

I need additional disk space for storing my backups on SQL server 2000. Any
ideas on the latest and greatest discs I can purchase. ThanksWhat disks you can buy probably depends on your hardware. Are you running
IDE? SATA? SCSI? Buy whatever disks work with your current hardware,
provide enough storage, and are cost effective for your environment. Don't
forget about growth...you probably don't want to buy drives in another
month or two.
Keith
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:8A8A8D80-7E98-43DA-B78C-3EC3232DB8FF@.microsoft.com...
> I need additional disk space for storing my backups on SQL server 2000.
Any
> ideas on the latest and greatest discs I can purchase. Thanks|||Any limits that sql server has as far as how many discs you can add? I stil
l
have 4 slots open. We have SCSI discs some are 72.8 GB others are 36.4GB
Thanks
"Keith Kratochvil" wrote:

> What disks you can buy probably depends on your hardware. Are you running
> IDE? SATA? SCSI? Buy whatever disks work with your current hardware,
> provide enough storage, and are cost effective for your environment. Don'
t
> forget about growth...you probably don't want to buy drives in another
> month or two.
> --
> Keith
>
> "Niles" <Niles@.discussions.microsoft.com> wrote in message
> news:8A8A8D80-7E98-43DA-B78C-3EC3232DB8FF@.microsoft.com...
> Any
>|||SQL Server will use what the OS provides.
Keith
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:0044BB2C-7897-4DA6-9237-AED43D7F35AE@.microsoft.com...
> Any limits that sql server has as far as how many discs you can add? I
still[vbcol=seagreen]
> have 4 slots open. We have SCSI discs some are 72.8 GB others are 36.4GB
> Thanks
> "Keith Kratochvil" wrote:
>
running[vbcol=seagreen]
Don't[vbcol=seagreen]
2000.[vbcol=seagreen]sql

Disk space usage

SQL 7.0 SP4/NT 4.0
DB File is set to grow by 5%.
It's given that SQL preallocates its hard disk space so
that as records are added to the table, there is no
change to the hard drive space at the NT level.
Is there a way to see, at the SQL level, how much of the
space currently allocated, is available?
Thanks,
Donsp_spaceused will show you how much space is currently
allocated in a database.
Linchi
>--Original Message--
>SQL 7.0 SP4/NT 4.0
>DB File is set to grow by 5%.
>It's given that SQL preallocates its hard disk space so
>that as records are added to the table, there is no
>change to the hard drive space at the NT level.
>Is there a way to see, at the SQL level, how much of the
>space currently allocated, is available?
>Thanks,
>Don
>
>.
>

DISK SPACE QUERY

Hello, is there a way to make a sql statement that checks disk space and
returns a value if it is below x%?Hi,
See the below query, values for each drive will returned in mega bytes.
create table space_check (drive_letter char(1), space_free int)
insert into tmp exec xp_fixeddrives
select * from space_check where space_free < 20
Thanks
Hari
SQL Server MVP
"Gervaise" <Gervaise@.discussions.microsoft.com> wrote in message
news:86C35096-4F7D-4CBB-8A1A-9BDBF028F0E7@.microsoft.com...
> Hello, is there a way to make a sql statement that checks disk space and
> returns a value if it is below x%?

disk space occupied by certain rows?

is there a way to get the disk space occupied by certain rows?

Thank You.

Regards,

A Friend.

SELECT DATALENGTH(ColA) + DATALENGTH(ColB)
From SomeTable
Where Conditionhere

Jens k. Suessmeyer.

http://www.sqlserver2005.de

Disk space Issues

Good day,
I installed sql server 2000 on a 10Gig disk. Now i have a fast growing
database. Is there a way to chanel the incoming data to an already installed
40Gig disk on thesame system?
--
Michael AligbeYou could back up your database, drop it, and then restore it with the "WITH
MOVE" option. This would allow you to place your ldf and/or mdf files on a
different drive.
The other option would be to add filegroups to your database and move some
of your data to those new filegroups. This might be a little more involved
than option one however.
"Myk" wrote:
> Good day,
> I installed sql server 2000 on a 10Gig disk. Now i have a fast growing
> database. Is there a way to chanel the incoming data to an already installed
> 40Gig disk on thesame system?
> --
> Michael Aligbe|||Another way is to sp_detach_db dbname,
copy the mdf file to the other hard drive, the
sp_attach_db dbname, mdffilename, logfilename
This would be quicker than backing up and restoring
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Myk" <Myk@.discussions.microsoft.com> wrote in message
news:00085AD1-BF75-4066-9C12-3D2BAF27DB14@.microsoft.com...
> Good day,
> I installed sql server 2000 on a 10Gig disk. Now i have a fast growing
> database. Is there a way to chanel the incoming data to an already
installed
> 40Gig disk on thesame system?
> --
> Michael Aligbe

Disk space Issues

Good day,
I installed sql server 2000 on a 10Gig disk. Now i have a fast growing
database. Is there a way to chanel the incoming data to an already installed
40Gig disk on thesame system?
Michael Aligbe
You could back up your database, drop it, and then restore it with the "WITH
MOVE" option. This would allow you to place your ldf and/or mdf files on a
different drive.
The other option would be to add filegroups to your database and move some
of your data to those new filegroups. This might be a little more involved
than option one however.
"Myk" wrote:

> Good day,
> I installed sql server 2000 on a 10Gig disk. Now i have a fast growing
> database. Is there a way to chanel the incoming data to an already installed
> 40Gig disk on thesame system?
> --
> Michael Aligbe
|||Another way is to sp_detach_db dbname,
copy the mdf file to the other hard drive, the
sp_attach_db dbname, mdffilename, logfilename
This would be quicker than backing up and restoring
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Myk" <Myk@.discussions.microsoft.com> wrote in message
news:00085AD1-BF75-4066-9C12-3D2BAF27DB14@.microsoft.com...
> Good day,
> I installed sql server 2000 on a 10Gig disk. Now i have a fast growing
> database. Is there a way to chanel the incoming data to an already
installed
> 40Gig disk on thesame system?
> --
> Michael Aligbe
sql

Disk space Issues

Good day,
I installed sql server 2000 on a 10Gig disk. Now i have a fast growing
database. Is there a way to chanel the incoming data to an already installed
40Gig disk on thesame system?
--
Michael AligbeYou could back up your database, drop it, and then restore it with the "WITH
MOVE" option. This would allow you to place your ldf and/or mdf files on a
different drive.
The other option would be to add filegroups to your database and move some
of your data to those new filegroups. This might be a little more involved
than option one however.
"Myk" wrote:

> Good day,
> I installed sql server 2000 on a 10Gig disk. Now i have a fast growing
> database. Is there a way to chanel the incoming data to an already install
ed
> 40Gig disk on thesame system?
> --
> Michael Aligbe|||Another way is to sp_detach_db dbname,
copy the mdf file to the other hard drive, the
sp_attach_db dbname, mdffilename, logfilename
This would be quicker than backing up and restoring
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Myk" <Myk@.discussions.microsoft.com> wrote in message
news:00085AD1-BF75-4066-9C12-3D2BAF27DB14@.microsoft.com...
> Good day,
> I installed sql server 2000 on a 10Gig disk. Now i have a fast growing
> database. Is there a way to chanel the incoming data to an already
installed
> 40Gig disk on thesame system?
> --
> Michael Aligbe

Disk space information in SQL Server 2005.

As a normal sql user we are not able to get the disk space information by

executing 'xp_fixeddrive' extended stored procedure.

We are able to get the result using sa user and windows authenticated user.

Any help will be appreciated.

You may get the information you seek by querying: sys.sysfiles,

or perhaps using the dynamic management view: sys.dm_db_file_space_usage

Refer to Books Online for more information about either of them.

|||the xp_fixeddrives can get the free space in the disk(drivers). it is different to sys.sysfiles.

|||do you have the permission to run this extend procedure? any error informaiton that you got when you used a normall sql user?

I think it caused by the permission. .when you login as sa or windows administrator account , it belongs to sysadmin group by default.

Disk space getting low

I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
How do I get the 2 databases to use drive D in addition to drive E for data?
I have 50 gig volume for E and I have about 9 gig free as of now. D has
only consumed 1 gig and free is 34 gig.
TIA
__StephenPre-allocate storage for the database files as desired. If you need to shrink one of the file (so
that data will be pushed to the other file), you can do it using DBCC SHRINKFILE. It will take a
while for a lot of data, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:ugcOCCvsFHA.3040@.TK2MSFTNGP14.phx.gbl...
>I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
> How do I get the 2 databases to use drive D in addition to drive E for data? I have 50 gig volume
> for E and I have about 9 gig free as of now. D has only consumed 1 gig and free is 34 gig.
> TIA
> __Stephen
>
>

Disk space getting low

I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
How do I get the 2 databases to use drive D in addition to drive E for data?
I have 50 gig volume for E and I have about 9 gig free as of now. D has
only consumed 1 gig and free is 34 gig.
TIA
__Stephen
Pre-allocate storage for the database files as desired. If you need to shrink one of the file (so
that data will be pushed to the other file), you can do it using DBCC SHRINKFILE. It will take a
while for a lot of data, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:ugcOCCvsFHA.3040@.TK2MSFTNGP14.phx.gbl...
>I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
> How do I get the 2 databases to use drive D in addition to drive E for data? I have 50 gig volume
> for E and I have about 9 gig free as of now. D has only consumed 1 gig and free is 34 gig.
> TIA
> __Stephen
>
>

Disk space getting low

I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
How do I get the 2 databases to use drive D in addition to drive E for data?
I have 50 gig volume for E and I have about 9 gig free as of now. D has
only consumed 1 gig and free is 34 gig.
TIA
__StephenPre-allocate storage for the database files as desired. If you need to shrin
k one of the file (so
that data will be pushed to the other file), you can do it using DBCC SHRINK
FILE. It will take a
while for a lot of data, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:ugcOCCvsFHA.3040@.TK2MSFTNGP14.phx.gbl...
>I have 3 disk volumes on my server. C, D, E. C=System, D=Logs, E= Data
> How do I get the 2 databases to use drive D in addition to drive E for dat
a? I have 50 gig volume
> for E and I have about 9 gig free as of now. D has only consumed 1 gig a
nd free is 34 gig.
> TIA
> __Stephen
>
>sql

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
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...
>
>

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..
.
>
>

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
>
>

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

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

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 allocation problem -- Negative Un allocated space

For the past few days we are facing problem in some of our
databases. The no of dead locks are increasing and when we
check the database size with SP_spaceused, the amount of
unallocated space is showing a negative value. Actually,
there is lot of unused space also. The database has been
configure to autogrow.
We are surprised to see the negative Un-allocated space as
normaly system should allocate new extents if the space is
not available. When we used sp_spaceused @.updateusage
= 'true', then the allocation is comming alright.
Any one who has faced the same problem may please share
how to solve this permanently.This is a known issue with sp_spaceused. Also look into using DBCC
UPDATEUSAGE.
Note, this may not be related to why you are seeing deadlocks.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Lokesh" <lokesh.raju@.moserbaer.net> wrote in message
news:07db01c35366$261732d0$a101280a@.phx.gbl...
For the past few days we are facing problem in some of our
databases. The no of dead locks are increasing and when we
check the database size with SP_spaceused, the amount of
unallocated space is showing a negative value. Actually,
there is lot of unused space also. The database has been
configure to autogrow.
We are surprised to see the negative Un-allocated space as
normaly system should allocate new extents if the space is
not available. When we used sp_spaceused @.updateusage
= 'true', then the allocation is comming alright.
Any one who has faced the same problem may please share
how to solve this permanently.|||I'd like to rely on taskpad view for quick examination of db size, or run my
customized stored proc or function to get the correct figures in real time
(use DBCC SHOWFILESIZE" and "DBCC SQLPERF(LOGSPACE)").
I do NOT need to run DBCC UPDATEUSAGE like I had to with sp_spaceused.
Richard
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:eonUPt2UDHA.1836@.TK2MSFTNGP09.phx.gbl...
> This is a known issue with sp_spaceused. Also look into using DBCC
> UPDATEUSAGE.
> Note, this may not be related to why you are seeing deadlocks.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> What hardware is your SQL Server running on?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Lokesh" <lokesh.raju@.moserbaer.net> wrote in message
> news:07db01c35366$261732d0$a101280a@.phx.gbl...
> For the past few days we are facing problem in some of our
> databases. The no of dead locks are increasing and when we
> check the database size with SP_spaceused, the amount of
> unallocated space is showing a negative value. Actually,
> there is lot of unused space also. The database has been
> configure to autogrow.
> We are surprised to see the negative Un-allocated space as
> normaly system should allocate new extents if the space is
> not available. When we used sp_spaceused @.updateusage
> = 'true', then the allocation is comming alright.
> Any one who has faced the same problem may please share
> how to solve this permanently.
>
>|||If you want to rely on the GUI, then run DBCC UPDATEUSAGE on a regular basis
(like as part of stats update or index defrag jobs would be good) and run it
after any major changes (like copying in or out a bunch of data, or if the
thing gets full)
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:%23g91osFVDHA.2068@.TK2MSFTNGP11.phx.gbl...
> I'd like to rely on taskpad view for quick examination of db size, or run
my
> customized stored proc or function to get the correct figures in real time
> (use DBCC SHOWFILESIZE" and "DBCC SQLPERF(LOGSPACE)").
> I do NOT need to run DBCC UPDATEUSAGE like I had to with sp_spaceused.
>
> Richard
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:eonUPt2UDHA.1836@.TK2MSFTNGP09.phx.gbl...
> > This is a known issue with sp_spaceused. Also look into using DBCC
> > UPDATEUSAGE.
> >
> > Note, this may not be related to why you are seeing deadlocks.
> >
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > What hardware is your SQL Server running on?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> >
> >
> > "Lokesh" <lokesh.raju@.moserbaer.net> wrote in message
> > news:07db01c35366$261732d0$a101280a@.phx.gbl...
> > For the past few days we are facing problem in some of our
> > databases. The no of dead locks are increasing and when we
> > check the database size with SP_spaceused, the amount of
> > unallocated space is showing a negative value. Actually,
> > there is lot of unused space also. The database has been
> > configure to autogrow.
> >
> > We are surprised to see the negative Un-allocated space as
> > normaly system should allocate new extents if the space is
> > not available. When we used sp_spaceused @.updateusage
> > = 'true', then the allocation is comming alright.
> >
> > Any one who has faced the same problem may please share
> > how to solve this permanently.
> >
> >
> >
> >
>

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
>