SQL: Check Free Space
Author: jason
Date: 2014-05-14 10:44:23
Category: Technical

I was trying to figure out how much free space each database file had and ran across the below article. I added sp_MSforeachdb and some more math to get the Percentage...

Article: http://www.mssqltips.com/sqlservertip/1805/different-ways-to-determine-free-space-for-sql-server-databases-and-database-files/





Code

SELECT DB_NAME() AS DbName
, name AS FileName, growth, is_percent_growth
, size/128.0 AS CurrentSizeMB
, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
, ((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) / (size/128.0)) * 100 AS PercentFree
FROM sys.database_files;







Code

CREATE TABLE #DBSize (DBName VARCHAR(255), [FileName] VARCHAR(255)
, Growth INT, is_percent_growth BIT
, CurrentSizeMB DECIMAL(18,2), FreeSpaceMB DECIMAL(18,2)
, PercentFree DECIMAL(18,2))

EXEC sp_MSforeachdb 'USE [?]
INSERT INTO #DBSize
SELECT DB_NAME() AS DbName
, name AS FileName, growth, is_percent_growth
, size/128.0 AS CurrentSizeMB
, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB
, ((size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0) / (size/128.0)) * 100 AS PercentFree
FROM sys.database_files; ';

SELECT * FROM #DBSize
--WHERE PercentFree <= 25
ORDER BY PercentFree ASC

DROP TABLE #DBSize





jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009