Best way to find free space in SQL servers?

Question

I want to find out how much space is left in my database files so that I can know when to increase the space so that it does not do this when the application is running.

I would prefer to be able to script so that I can run it regularly across multiple databases.

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

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

Answer 1

Try sp_spaceused :

Displays the number of rows, disks, reserved space, and disk space used by the table, indexed view, or service Broker queue in the current database, or displays the reserved disk space and is used by the entire database.

I believe this was in SQL Server 2000, but I cannot prove it. He works in 2005 and 2008.

And you can look into it with sp_helptext if you want to associate it with some kind of server-side logic.

EDIT : extending my comment below and thanking the original contributor http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82359 , here's a way to break 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

You can run SQL scripts to check free space manually. But it’s better to create tasks and set alerts to automatically inform the administrator when there is little space left.

The best article I have found is Managing Database Data Using Custom Space Alerts (requires SQLServerCentral Login), even support staff can be provided without much DBA experience.

You can also use a similar article to track database file sizes using SQL Server Jobs .

Also for manual verification, I prefer sp_SOS (can be downloaded from http://searchsqlserver.techtarget.com/tip/Find-size-of-SQL-Server-tables-and-other-objects-with-stored-procedure )

 EXEC dbo.sp_SOS @OrderBy='T' 

Answer 3

Run the command below to find out how much free space is currently available on 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 

Answer 4

For me, the sql server always automatically allocated more space until the hard drive was full, so just request free space on your hard drive.

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