Introduction
Ever have a need to calculate the total available storage space for a server? Recently I did. Here's a solution I came up with - I bet someone can do this better!
xp_fixeddrives
There's a handy stored procedure called xp_fixeddrives that reports the available storage space:
exec xp_fixeddrives
This returns:
drive MB free
----- -----------
C 6998
E 201066
Problem solved right? Maybe.
The Sum
What I really want is the sum total of all available space presented to the server. I built this to accomplish that:
declare @tbl table
(drive char(1)
,MBFree int)
First I built a table variable to hold the results of xp_fixeddrives. Then I populated it with an INSERT statement:
insert
into @tbl
exec xp_fixeddrives
To test, I execute a SELECT statement:
select
* from @tbl
Finally, I execute a SELECT SUM to gather the total storage space available:
select sum(MBFree) as ServerDriveSpace
from @tbl
The result is:
ServerDriveSpace
----------------
208064
There are other ways to approach this. How would you do it?
:{> Andy