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

sqlsql-serversql-server-2005

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:

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

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Best Solution

Related Solutions

Sql-server – How to specify a different port number in SQL Management Studio

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Related Question