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


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.