Thursday, June 02, 2011

Find available space on Data & Log file on sql server database

-- Find free data space on master database
use master

go
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

-- Find free data space on database
use AdventureWorks2008 -- ( yourdbname )
go
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

-- Find free log space on database
DBCC SQLPERF(LOGSPACE);