SQL Script to show available space for all Databases

22 May

Quick little script to return the space available on all databases. I linked this into PRTG Network Monitor to make sure that none of them were too low.

--///////////////////////////////////////////////////////////////////////////////////////////
--////////////////////// Filespace Used and Free   //////////////////////////////////////////
IF (OBJECT_ID('tempdb..#tmpDbSpace') IS NOT NULL)
      DROP TABLE #tmpDbSpace

CREATE TABLE #tmpDbSpace (
      [DBname] varchar (126) NULL, 
      [LogicalFileName] varchar (126) NULL,
      [IsLogFile] tinyint NULL,
      [IsPrimaryFile] tinyint NULL,
      [IsReadOnly] tinyint NULL,
      [FileSizeMB] numeric (12, 2) NULL,
      [SpaceUsedMB] numeric (12, 2) NULL,
      [Growth] varchar (50) NULL)

DECLARE @TSQL varchar (max)

SET @TSQL = 'USE [?]; INSERT INTO #tmpDbSpace
                        SELECT
                              [DBName]=DB_NAME(DB_ID()),
                              [LogicalFileName]=[name],
                              [IsLogFile]=FILEPROPERTY([name], ''IsLogFile''),
                              [IsPrimaryFile]=FILEPROPERTY([name], ''IsPrimaryFile''),
                              [IsReadOnly]=FILEPROPERTY([name], ''IsReadOnly''),
                              [FileSizeMB]=([Size]*8)/1024, -- In pages of 8 Kb
                              [SpaceUsedMB]=(FILEPROPERTY([name], ''SpaceUsed'')*8)/1024, -- In pages of 8 Kb
                              [Growth] = CASE status & 0x100000
                              WHEN 0x100000 THEN      CONVERT(VARCHAR(15), growth) + ''%''
                              ELSE CONVERT(VARCHAR(15), CONVERT (BIGINT, growth) * 8) + '' KB'' END
                              FROM sys.sysfiles'

--Print the command to be issued against all databases  
PRINT @TSQL  

--Run the command against each database  
EXEC sp_MSforeachdb @TSQL 

SELECT
[DBname],  
[LogicalFileName],
[FileType]=CASE
WHEN [IsLogFile]=1 THEN 'Log'
WHEN [IsLogFile]=0 AND [IsPrimaryFile]=1 THEN 'Primary'
ELSE 'Data'
END,
[FileSizeMB],
[SpaceUsedMB],
[FreeSpacePercent]=CASE
WHEN [FileSizeMB] = 0 THEN 0
ELSE CAST((([FileSizeMB]-[SpaceUsedMB])/[FileSizeMB])*100 AS numeric (12, 2))
END,
[Growth]
FROM #tmpDbSpace
ORDER BY [DBname], [IsLogFile]
--///////////////////////////////////////////////////////////////////////////////////////////