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 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 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

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

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_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:

ServerDriveSpace
----------------
208064

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

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

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

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