Sql – Best way to find free space in sql server databases


I want to be able to find out how much space is left in my database files so that I can know when to increase the space so it doesn't do it when the application is running hard out.

I would prefer to be able to script this so I can run it across multiple databases ion a regular basis.

I have SQL Server 2000 and SQL Server 2005 databases but I would prefer to be able to run the same script across both.

I can use Management Studio to do this manually on 2005 databases, but not on the 2000 databases.

Best Solution

Try sp_spaceused:

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

I believe that this was present in SQL Server 2000, but I can't prove it. It works in 2005 and 2008.

And you can peek at it with sp_helptext, if you want to tie it into some server-side logic.

EDIT: expanding on my comment below, and with thanks to the original contributor at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82359, here's a way to break down usage by file:

    , filename
    , convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
    , convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
    , convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a