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

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

Answer 2

Run the below command to know how much free space currently available in your SQL Server 2000:

DECLARE @command VARCHAR(5000)   
DECLARE @DBInfo TABLE   
( ServerName VARCHAR(100),   
DatabaseName VARCHAR(100),   
PhysicalFileName NVARCHAR(520),   
FileSizeMB DECIMAL(10,2),   
SpaceUsedMB DECIMAL(10,2),   
FreeSpaceMB DECIMAL(10,2), 
FreeSpacePct varchar(8) 
) 

SELECT @command = 'Use [' + '?' + '] SELECT   
@@servername as ServerName,   
' + '''' + '?' + '''' + ' AS DatabaseName   , 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, 
    CAST(100 * (CAST (((a.size/128.0 -CAST(FILEPROPERTY(a.name,' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(a.size/128.0)) AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct 
from dbo.sysfiles a' 

INSERT INTO @DBInfo 
EXEC sp_MSForEachDB @command   

SELECT * from @DBInfo