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



    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],



    dbo.sysfiles a


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

1 comment:

Anonymous said...

Very cool.