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 CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; 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 Managing Geeks - A Journey of Leading by Doing, and author of 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



Friend of Red Gate

My Company

Blog Roll

Check out the Linchpin People Blog...
Linchpin People Blog

Contact Me


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