How do I find Total Disk Size from SQLServer

I know you can use xp_fixeddrives to find the free space left in the disks on
the sql server box.
I trying to write a procedure where I can set a threshold in the DB for each
disks and once we reach it send me an email. Now I have the Disk Size as
hard coded, the problem with this is that if we changes disks or use this
procedure on another box running SQL Server, it not going to be accurate. Is
there anyway to find total disk size using some XP's like xp_fixeddrives.
Thanks.
This was given by David Portas
David Portas
Sep 9 2003, 12:48 am show options
Newsgroups: microsoft.public.sqlserver.server
From: "David Portas" <REMOVE_BEFORE_REPLYING_dpor...@.acm.org> - Find
messages by this author
Date: Tue, 9 Sep 2003 09:48:54 +0100
Local: Tues, Sep 9 2003 12:48 am
Subject: Re: reporting total disk space
Reply to Author | Forward | Print | Individual Message | Show original
| Report Abuse
This function will give you total space for any given drive:
CREATE FUNCTION dbo.GetDriveSize
(@.driveletter CHAR(1))
RETURNS NUMERIC(20)
BEGIN
DECLARE @.rs INTEGER, @.fso INTEGER, @.getdrive VARCHAR(13), @.drv
INTEGER,
@.drivesize VARCHAR(20)
SET @.getdrive = 'GetDrive("' + @.driveletter + '")'
EXEC @.rs = sp_OACreate 'Scripting.FileSystemObject', @.fso OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAMethod @.fso, @.getdrive, @.drv OUTPUT
IF @.rs = 0
EXEC @.rs = sp_OAGetProperty @.drv,'TotalSize', @.drivesize OUTPUT
IF @.rs<> 0
SET @.drivesize = NULL
EXEC sp_OADestroy @.drv
EXEC sp_OADestroy @.fso
RETURN @.drivesize
END
GO
SELECT dbo.GetDriveSize('C')
|||sp_diskspace
http://www.sqldbatips.com/displaycode.asp?ID=4
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>I know you can use xp_fixeddrives to find the free space left in the disks
>on
> the sql server box.
> I trying to write a procedure where I can set a threshold in the DB for
> each
> disks and once we reach it send me an email. Now I have the Disk Size as
> hard coded, the problem with this is that if we changes disks or use this
> procedure on another box running SQL Server, it not going to be accurate.
> Is
> there anyway to find total disk size using some XP's like xp_fixeddrives.
> Thanks.
|||Thanks guys,
Since the user who needs to use these procedures is noy sysadmin, I'm
explicitly granting execute permissions in the 4 SP's being used. Hoep
that;s not a big issue.
"Jasper Smith" wrote:

> sp_diskspace
> http://www.sqldbatips.com/displaycode.asp?ID=4
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "yodarules" <yodarules@.discussions.microsoft.com> wrote in message
> news:472C3AFD-B322-426C-A7CF-0E2B0A4A75EB@.microsoft.com...
>
>