how to get disk total space info?

hi all,
I know that master..xp_fixeddrives can give you the free space info for each
drive on your sql server.
but is there any proc that can return the total disk space?
also, anyone know how to see the code inside of master..xp_fixeddrives?
many thanks!!
JJI don;t knoiw of any way for SQL t odo that... But...
Don't you know how big the disks are ?
These values will not change often.
Create a table and put this data in it...
"JJ Wang" wrote:

> hi all,
> I know that master..xp_fixeddrives can give you the free space info for ea
ch
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||xp_fixeddrives is an extended stored proc. written in C/C++ and compiled.
It most likely calls the GetDiskFreeSpaceEx API function to get the free
space. You could always write your own extended stored proc to make the
same call, but utilize the other information returned like total disk size.
Here's more info. on that:
http://support.microsoft.com/defaul...b;en-us;231497.
It might be easier just to do it from your client app - most languages have
easier ways to get at this particular info., and I think I read that SQL
2005 will also offer easier methods to access this - but someone else will
need to give you specifics on that.
"JJ Wang" <JJ Wang@.discussions.microsoft.com> wrote in message
news:B388A868-9058-45D9-A99F-38BD00B8B0B4@.microsoft.com...
> hi all,
> I know that master..xp_fixeddrives can give you the free space info for
> each
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||CREATE FUNCTION dbo.GetDriveSize (@.driveletter CHAR(1))
/* Returns the total capacity for any drive letter */
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')
David Portas
SQL Server MVP
--|||thank you guys so much for all your useful tips!!
Dave,
thank you so much for the function of yours, it works like a charm!!
have you been using this in your production servers? is there anything I
should look out in this function?
many thanks!!
JJ
"JJ Wang" wrote:

> hi all,
> I know that master..xp_fixeddrives can give you the free space info for ea
ch
> drive on your sql server.
> but is there any proc that can return the total disk space?
> also, anyone know how to see the code inside of master..xp_fixeddrives?
> many thanks!!
> JJ|||I have used this in production - not as something to be called
regularly but just as required to refresh a table of drive sizes. I
agree with CBretana that it makes sense to retain this information in a
table but in a large SAN environment with many drive arrays (we were
managing about 15 terabytes across 6 servers) it can be a difficult to
keep that information accurate and up-to-date.
You need to be wary about memory and resource leaks when calling COM
objects with sp_OACreate. I haven't had problems with this one (that's
under SP3, which I assume you are at) but do test it out in your
environment.
David Portas
SQL Server MVP
--|||hi Dave,
thanks again for the wonderful info!
yes, our sql servers are at the highest sp.
how long have you been using this function on your prod servers? and how
often do you refresh your drive size table? Sounds like 'daily' is a bad
idea to run the com objects, huh? :-)
how much memory and cpu in avg do your servers have(which you run this
function on)?
thanks Dave!
JJ
"David Portas" wrote:

> I have used this in production - not as something to be called
> regularly but just as required to refresh a table of drive sizes. I
> agree with CBretana that it makes sense to retain this information in a
> table but in a large SAN environment with many drive arrays (we were
> managing about 15 terabytes across 6 servers) it can be a difficult to
> keep that information accurate and up-to-date.
> You need to be wary about memory and resource leaks when calling COM
> objects with sp_OACreate. I haven't had problems with this one (that's
> under SP3, which I assume you are at) but do test it out in your
> environment.
> --
> David Portas
> SQL Server MVP
> --
>