April 23, 2009

Check File Size, Space Used and Free Space of a Database using T-SQL




I was looking around for a way to find out the Free Space in a Database using T-SQL. Luckily, I bumped into this very cool script that gives you important info like File Size, Space Used and Free Space of a Database using T-SQL. This script was written by Michael Valentine Jones and I am just sharing it with you (since it's way cool!)


USE Northwind


SELECT


    a.FILEID,


    CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,


    CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [SPACEUSEDINMB],


    CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [FREESPACEINMB],


    a.name as [DATABASENAME],


    a.FILENAME as [FILENAME]


FROM


    dbo.sysfiles a




OUTPUT


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

1 Response to "Check File Size, Space Used and Free Space of a Database using T-SQL"
  1. Anonymous said...
    January 4, 2011 at 9:06 PM

    Very cool.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions