Occasionally you need to take inventory of how much free space each database has.
You can find this quickly and easily in Database Health Monitor, but if you want to get it programmatically you can do it with the following query.
DECLARE @databaseList as CURSOR;
DECLARE @databaseName as NVARCHAR(500);
DECLARE @tsql AS NVARCHAR(2000);
CREATE TABLE ##FreeSpace
(
[DbName] varchar(1000),
[FreeSpaceInMb] DECIMAL(12,2),
[Name] varchar(1000),
[Filename] varchar(1000)
);
SET @databaseList = CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT QUOTENAME([name])
FROM master.dbo.sysdatabases -- FOR SQL Server 2000 if you are doing archeological sql work. there is no sys.databases
WHERE DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' -- version will be zero if the database is offline.
and [name] <> 'tempdb'
OPEN @databaseList;
FETCH NEXT FROM @databaseList into @databaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tsql = N'
USE ' + @databaseName + ';
INSERT INTO ##FreeSpace
SELECT ''' + @databaseName + ''' as DbName,
CAST(CONVERT(DECIMAL(12,2),
Round((t1.size-Fileproperty(t1.name,''SpaceUsed''))/128.000,2)) AS VARCHAR(10)) AS [FreeSpaceMB],
CAST(t1.name AS VARCHAR(500)) AS [Name],
Filename
FROM ' + @databaseName + '.dbo.sysfiles t1;';
EXECUTE (@tsql);
FETCH NEXT FROM @databaseList into @databaseName;
END
CLOSE @databaseList;
DEALLOCATE @databaseList;
SELECT * FROM ##FreeSpace
ORDER BY FreeSpaceInMb desc;
DROP TABLE ##FreeSpace;
This query has been tested on SQL Server 2000, 2005, 2008, 2008R2, 2012, 2014, 2016, 2017, and 2019. This references master.dbo.sysdatabases instead of sys.databases since sys.databases didn’t exist on SQL Server 2000, and I had to make this work on 2000 for one client. Works great on SQL Server 2019, which is where I have used this the most lately.
I hope you find this useful.
-Steve
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
This query results in a lot of error messages on my SQL Server 2019 system:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘128.0’.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘A’.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier “t1.size” could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier “t1.name” could not be bound.
Msg 213, Level 16, State 1, Line 3
Column name or number of supplied values does not match table definition.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘VARCHA’.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘(‘.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ’10’.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘A’.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘SpaceUsed’.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘SpaceUsed’.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘VARCHA’.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘)’.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘VARCHA’.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘VARCHA’.
There was an error on the sizing of the @tsql NVARCHAR as being 200 chars when it should have been 2000 chars.
It is fixed now, please give it another try.
-Steve
Hello.
Check the script, it corrupts.
Corrupt may be a bit of a strong word for a typo.
There was an error on the sizing of the @tsql NVARCHAR as being 200 chars when it should have been 2000 chars.
It is fixed now, please give it another try.
-Steve
A variation of the same general process using sp_MSForeachdb
IF ISNULL(OBJECT_ID(‘tempdb..#tblDBInfo’),0) 0
DROP TABLE #tblDBInfo
CREATE TABLE #tblDBInfo
(
DBName varchar(128)
,FileName varchar(128)
,FileType varchar(10)
,SizeMB bigint
,UsedMB bigint
,FreeMB bigint
,FreePct numeric(5,2)
)
INSERT #tblDBInfo
EXEC master..sp_MSForeachdb ‘
USE [?];select DB_NAME()
,name
,type_desc
,size/128
,FILEPROPERTY(name,”SpaceUsed”)/128
,FreeSpace = (size – FILEPROPERTY(name,”SpaceUsed”))/128
,FreeSpacePct = ((size – FILEPROPERTY(name,”SpaceUsed”))*100.0)/size
from sys.database_files
WHERE type_desc ”LOG”’
SELECT CASE
WHEN FreePct >= 30 THEN 1
WHEN FreePct >= 20 THEN 2
ELSE 3 END
,’Free space on ‘ + DBName + ‘ greater than 10% – ‘
+ CONVERT(varchar(10),FreeMB) + ‘MB free (‘ + CONVERT(varchar(10),FreePct) + ‘%)’
FROM #tblDBInfo
Thanks for your post David. I try to stay away from the sp_MSForEachDB for 2 reasons, one is that it is undocumented and not supported (although that is not that big of a deal). The second reason is that it occasionally misses databases and should perhaps be called sp_MSForEachDBMostOfTheTimeButSometimesNot.
The cursor method is much more reliable if it is really important to see all the databsaes.
-Steve