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_fixeddrivesThis 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
Maybe also add a little db info:
Select T.drive , DB_NAME(F.dbid) as DbName, AVG(T.MBFree ) as MBFree
from @tbl T
left join master.sys.sysaltfiles F
on F.filename like T.drive +’%’
group by T.drive , DB_NAME(dbid)
order by T.drive
On clustered instances, your instance may not have access to all available drives but xp_fixeddrives will list all !
And maybe even add file type info ….
Select @@ServerName as ServerName
, T.drive
, DB_NAME(F.dbid) as DbName
, right(F.filename, charindex(N’.’, reverse(F.filename))) as FileType
, AVG(T.MBFree) as MBFree
from @tbl T
left join master.sys.sysaltfiles F
on F.filename like T.drive + ‘%’
group by T.drive
, DB_NAME(dbid)
, right(F.filename, charindex(N’.’, reverse(F.filename)))
order by T.drive
, DbName
, FileType
I don’t believe xp_fixeddrives works with mount points. Been awhile since I looked at this though. Some of our clusters have their "drives" attached as mount points under a common folder. xp_fixeddrives only reports the root which only has a few MB free. Seems like PS or vbscript would be a better tool.
Here’s a PS script to show space (includes mount points)
gwmi Win32_Volume|where-object {$_.filesystem -match "ntfs"}|ft name,capacity,freespace
Don’t forget to add that xp_fixeddrives is an undocumented extended proc, so should be used in Production code with caution, if at all.
I played with this xp_ for some time with dmo/smo options.
Finally, decided to go with vbs and that gives a lot of details and flexibility.