Best way to find free space in sql server databases?

Question

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.

Answer 1

For me, sql server always automatically allocated more space until the harddrive was full, so just query the free space on the harddrive.

What settings do you use that you have to increase the "space" of a database?

Answer 2

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:

select
      name
    , 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