THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 AndyLeonard.blog.

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

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

http://andyleonard.blog/2010/05/27/t-sql-snack-how-much-free-storage-space-is-available/

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

Comments

 

ALZDBA said:

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

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

     , 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

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

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement