THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is a Data Philosopher at Andy Leonard Consulting, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and author of Managing Geeks - A Journey of Leading by Doing, and the Stairway to Integration Services.

T-SQL Snack: How Much Free Storage Space is Available?


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!


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:


There are other ways to approach this. How would you do it?

:{> Andy

Published Thursday, May 27, 2010 8:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



ALZDBA said:

Maybe also add a little db info:

Select , DB_NAME(F.dbid) as DbName, AVG(T.MBFree ) as MBFree

from @tbl T

left join master.sys.sysaltfiles  F

on F.filename like +'%'

group by , DB_NAME(dbid)

order by

May 27, 2010 8:02 AM

ALZDBA said:

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


     , 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 + '%'

group by

     , DB_NAME(dbid)

     , right(F.filename, charindex(N'.', reverse(F.filename)))

order by

     , DbName

     , FileType

May 27, 2010 8:16 AM

Dave Wentzel said:

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.    

May 27, 2010 2:16 PM

Peter said:

Here's a PS script to show space (includes mount points)

gwmi Win32_Volume|where-object {$_.filesystem -match "ntfs"}|ft name,capacity,freespace

May 28, 2010 5:11 AM

Chris Howarth said:

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.

May 29, 2010 4:07 AM

Jose Mathew said:

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.

May 30, 2010 11:25 PM

Leave a Comment


This Blog



My Company

Community Awards

Friend of Red Gate

Contact Me


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement