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 an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is

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

This blog has moved! You can find this content at the following new location:

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 Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement