DB file free used disk space capacity planning query

check_db_disk_space_free_used.sql

-- MOST USEFUL of all the scripts herein:


SET NOCOUNT ON
DECLARE @endDate datetime, @months smallint; 
SET @endDate = GetDate();  -- Data atual
SET @months = 12;          -- Nr. de meses a analisar

;WITH HIST AS 
   (SELECT BS.database_name AS DatabaseName 
          ,YEAR(BS.backup_start_date) * 100 
           + MONTH(BS.backup_start_date) AS YearMonth 
          ,CONVERT(numeric(10, 1), MIN(BS.backup_size / 1048576.0)) AS MinSizeMB 
          ,CONVERT(numeric(10, 1), MAX(BS.backup_size / 1048576.0)) AS MaxSizeMB 
          ,CONVERT(numeric(10, 1), AVG(BS.backup_size / 1048576.0)) AS AvgSizeMB 
    FROM msdb.dbo.backupset as BS 
    WHERE NOT BS.database_name IN 
              ('master', 'msdb', 'model', 'tempdb') 
          AND BS.type = 'D' 
          AND BS.backup_start_date BETWEEN DATEADD(mm, - @months, @endDate) AND     @endDate 
    GROUP BY BS.database_name 
            ,YEAR(BS.backup_start_date) 
            ,MONTH(BS.backup_start_date)) 
SELECT @@SERVERNAME
      ,MAIN.DatabaseName 
      ,MAIN.YearMonth 
      ,MAIN.MinSizeMB 
      ,MAIN.MaxSizeMB 
      ,MAIN.AvgSizeMB 
      ,MAIN.AvgSizeMB  
       - (SELECT TOP 1 SUB.AvgSizeMB 
          FROM HIST AS SUB 
          WHERE SUB.DatabaseName = MAIN.DatabaseName 
                AND SUB.YearMonth < MAIN.YearMonth 
          ORDER BY SUB.YearMonth DESC) AS GrowthMB 
FROM HIST AS MAIN 
ORDER BY MAIN.DatabaseName 
        ,MAIN.YearMonth









use master
go

IF OBJECT_ID('dbo.usp_Sizing') IS NOT NULL
	begin
	print 'dropping procedure dbo.usp_Sizing'
	drop procedure [dbo].[usp_Sizing]
	end
else
	begin
	print 'procedure dbo.usp_Sizing does not exist on ' + @@SERVERNAME
	end
go

CREATE PROCEDURE dbo.usp_Sizing @Granularity VARCHAR(10) = NULL, @Database_Name sysname = NULL AS   
DECLARE @SQL VARCHAR(5000)   

IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME = '##Results')    
   BEGIN    
       DROP TABLE ##Results    
   END   
      
CREATE TABLE ##Results ([Database Name] sysname, 
[File Name] sysname, 
[Physical Name] NVARCHAR(260),
[File Type] VARCHAR(4), 
[Total Size in Mb] INT, 
[Available Space in Mb] INT, 
[Growth Units] VARCHAR(15), 
[Max File Size in Mb] INT)   

SELECT @SQL =    
'USE [?] INSERT INTO ##Results([Database Name], [File Name], [Physical Name],    
[File Type], [Total Size in Mb], [Available Space in Mb],    
[Growth Units], [Max File Size in Mb])    
SELECT DB_NAME(),   
[name] AS [File Name],    
physical_name AS [Physical Name],    
[File Type] =    
CASE type   
WHEN 0 THEN ''Data'''    
+   
           'WHEN 1 THEN ''Log'''   
+   
       'END,   
[Total Size in Mb] =   
CASE ceiling([size]/128)    
WHEN 0 THEN 1   
ELSE ceiling([size]/128)   
END,   
[Available Space in Mb] =    
CASE ceiling([size]/128)   
WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)   
ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)   
END,   
[Growth Units]  =    
CASE [is_percent_growth]    
WHEN 1 THEN CAST(growth AS varchar(20)) + ''%'''   
+   
           'ELSE CAST(growth*8/1024 AS varchar(20)) + ''Mb'''   
+   
       'END,   
[Max File Size in Mb] =    
CASE [max_size]   
WHEN -1 THEN NULL   
WHEN 268435456 THEN NULL   
ELSE [max_size]   
END   
FROM sys.database_files   
ORDER BY [File Type], [file_id]'   

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

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

--UPDATE ##Results SET [Free Space %] = [Available Space in Mb]/[Total Size in Mb] * 100   

--Return the Results   
--If @Database_Name is NULL:   
IF @Database_Name IS NULL   
   BEGIN   
       IF @Granularity = 'Database'   
           BEGIN   
               SELECT    
               T.[Database Name],   
               T.[Total Size in Mb] AS [DB Size (Mb)],   
               T.[Available Space in Mb] AS [DB Free (Mb)],   
               T.[Consumed Space in Mb] AS [DB Used (Mb)],   
               D.[Total Size in Mb] AS [Data Size (Mb)],   
               D.[Available Space in Mb] AS [Data Free (Mb)],   
               D.[Consumed Space in Mb] AS [Data Used (Mb)],   
               CEILING(CAST(D.[Available Space in Mb] AS decimal(10,1))/D.[Total Size in Mb]*100) AS [Data Free %],   
               L.[Total Size in Mb] AS [Log Size (Mb)],   
               L.[Available Space in Mb] AS [Log Free (Mb)],   
               L.[Consumed Space in Mb] AS [Log Used (Mb)],   
               CEILING(CAST(L.[Available Space in Mb] AS decimal(10,1))/L.[Total Size in Mb]*100) AS [Log Free %]   
               FROM    
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   GROUP BY [Database Name]   
                   ) AS T   
                   INNER JOIN    
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   WHERE ##Results.[File Type] = 'Data'   
                   GROUP BY [Database Name]   
                   ) AS D ON T.[Database Name] = D.[Database Name]   
                   INNER JOIN   
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   WHERE ##Results.[File Type] = 'Log'   
                   GROUP BY [Database Name]   
                   ) AS L ON T.[Database Name] = L.[Database Name]   
               ORDER BY D.[Database Name]   
           END   
   ELSE   
       BEGIN   
           SELECT [Database Name],   
               [File Name],   
               [Physical Name],   
               [File Type],   
               [Total Size in Mb] AS [DB Size (Mb)],   
               [Available Space in Mb] AS [DB Free (Mb)],   
               CEILING(CAST([Available Space in Mb] AS decimal(10,1)) / [Total Size in Mb]*100) AS [Free Space %],   
               [Growth Units],   
               [Max File Size in Mb] AS [Grow Max Size (Mb)]    
           FROM ##Results    
       END   
   END   

--Return the Results   
--If @Database_Name is provided   
ELSE   
   BEGIN   
       IF @Granularity = 'Database'   
           BEGIN   
               SELECT    
               T.[Database Name],   
               T.[Total Size in Mb] AS [DB Size (Mb)],   
               T.[Available Space in Mb] AS [DB Free (Mb)],   
               T.[Consumed Space in Mb] AS [DB Used (Mb)],   
               D.[Total Size in Mb] AS [Data Size (Mb)],   
               D.[Available Space in Mb] AS [Data Free (Mb)],   
               D.[Consumed Space in Mb] AS [Data Used (Mb)],   
               CEILING(CAST(D.[Available Space in Mb] AS decimal(10,1))/D.[Total Size in Mb]*100) AS [Data Free %],   
               L.[Total Size in Mb] AS [Log Size (Mb)],   
               L.[Available Space in Mb] AS [Log Free (Mb)],   
               L.[Consumed Space in Mb] AS [Log Used (Mb)],   
               CEILING(CAST(L.[Available Space in Mb] AS decimal(10,1))/L.[Total Size in Mb]*100) AS [Log Free %]   
               FROM    
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   WHERE [Database Name] = @Database_Name   
                   GROUP BY [Database Name]   
                   ) AS T   
                   INNER JOIN    
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   WHERE ##Results.[File Type] = 'Data'   
                       AND [Database Name] = @Database_Name   
                   GROUP BY [Database Name]   
                   ) AS D ON T.[Database Name] = D.[Database Name]   
                   INNER JOIN   
                   (   
                   SELECT [Database Name],   
                       SUM([Total Size in Mb]) AS [Total Size in Mb],   
                       SUM([Available Space in Mb]) AS [Available Space in Mb],   
                       SUM([Total Size in Mb]-[Available Space in Mb]) AS [Consumed Space in Mb]    
                   FROM ##Results   
                   WHERE ##Results.[File Type] = 'Log'   
                       AND [Database Name] = @Database_Name   
                   GROUP BY [Database Name]   
                   ) AS L ON T.[Database Name] = L.[Database Name]   
               ORDER BY D.[Database Name]   
           END   
       ELSE   
           BEGIN   
               SELECT [Database Name],   
               [File Name],   
               [Physical Name],   
               [File Type],   
               [Total Size in Mb] AS [DB Size (Mb)],   
               [Available Space in Mb] AS [DB Free (Mb)],   
               CEILING(CAST([Available Space in Mb] AS decimal(10,1))/[Total Size in Mb]*100) AS [Free Space %],   
               [Growth Units],   
               [Max File Size in Mb] AS [Grow Max Size (Mb)]    
               FROM ##Results    
               WHERE [Database Name] = @Database_Name   
           END   
   END   
DROP TABLE ##Results  

go

IF OBJECT_ID('dbo.usp_Sizing') IS NOT NULL
	begin
	print 'Procedure dbo.usp_Sizing has been created'
	end
go


-- Additional queries:
-- first ##Results set from usp_Sizing followed by database detail of
-- complete last 12 months in asc order
DECLARE @SQL VARCHAR(5000) 

IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME = '##Results')    
   BEGIN    
       DROP TABLE ##Results    
   END 
      
CREATE TABLE ##Results ([Database Name] sysname, 
[File Name] sysname, 
[Physical Name] NVARCHAR(260),
[File Type] VARCHAR(4), 
[Total Size in Mb] INT, 
[Available Space in Mb] INT, 
[Growth Units] VARCHAR(15), 
[Max File Size in Mb] INT)   

SELECT @SQL =    
'USE [?] INSERT INTO ##Results([Database Name], [File Name], [Physical Name],    
[File Type], [Total Size in Mb], [Available Space in Mb],    
[Growth Units], [Max File Size in Mb])    
SELECT DB_NAME(),   
[name] AS [File Name],    
physical_name AS [Physical Name],    
[File Type] =    
CASE type   
WHEN 0 THEN ''Data'''    
+   
           'WHEN 1 THEN ''Log'''   
+   
       'END,   
[Total Size in Mb] =   
CASE ceiling([size]/128)    
WHEN 0 THEN 1   
ELSE ceiling([size]/128)   
END,   
[Available Space in Mb] =    
CASE ceiling([size]/128)   
WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)   
ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)   
END,   
[Growth Units]  =    
CASE [is_percent_growth]    
WHEN 1 THEN CAST(growth AS varchar(20)) + ''%'''   
+   
           'ELSE CAST(growth*8/1024 AS varchar(20)) + ''Mb'''   
+   
       'END,   
[Max File Size in Mb] =    
CASE [max_size]   
WHEN -1 THEN NULL   
WHEN 268435456 THEN NULL   
ELSE [max_size]   
END   
FROM sys.database_files   
ORDER BY [File Type], [file_id]'   

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

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

select *
from ##Results

exec dbo.usp_Sizing 'database'

select [Database Name], sum([Total Size in MB]) as TotalSizeMB, sum([Available Space in Mb]) as AvailSpaceMB
from ##Results
group by [Database Name]

-- Last 12 months of backups

SELECT 
msdb.dbo.backupset.database_name,
DATEPART(YEAR,backup_finish_date)  AS [BackupYear],
DATEPART(MONTH,backup_finish_date) AS [BackupMonth],
 (AVG(msdb.dbo.backupset.backup_size)/1048576) as [BackupSize (MB)]
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  msdb..backupset.type='D' 
and database_name not in ('master', 'model', 'msdb', 'tempdb')
and (DATEPART(YEAR,backup_finish_date) = DATEPART(YEAR,GETDATE()))
	or
	((DATEPART(YEAR,backup_finish_date) = DATEPART(YEAR,GETDATE()) - 1)
		and
	(DATEPART(MONTH,backup_finish_date) >= DATEPART(MONTH,GETDATE()) - 7))
GROUP BY msdb.dbo.backupset.database_name 
, DATEPART(MONTH,backup_finish_date) ,
DATEPART(YEAR,backup_finish_date) 
order by
msdb.dbo.backupset.database_name,
DATEPART(YEAR,backup_finish_date),
DATEPART(MONTH,backup_finish_date)



-- Current year's last 6 backup months (has 7 months bc
-- you need a previous month before the first as a calculation start)

SELECT 
msdb.dbo.backupset.database_name,
MAX(DATEPART(YEAR,backup_finish_date))  AS [BackupYear],
MAX(DATEPART(MONTH,backup_finish_date)) AS [BackupMonthMAX],
MIN(DATEPART(MONTH,backup_finish_date)) AS [BackupMonthMIN],
 (MAX(msdb.dbo.backupset.backup_size)/1048576) as [MAX BackupSize (MB)],
 (MIN(msdb.dbo.backupset.backup_size)/1048576) as [MIN BackupSize (MB)]
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  msdb..backupset.type='D' 
and database_name not in ('master', 'model', 'msdb', 'tempdb')
and DATEPART(YEAR,backup_finish_date)=DATEPART(YEAR,GETDATE())
and (DATEPART(MONTH,backup_finish_date) >= DATEPART(MONTH,GETDATE()) - 6)
GROUP BY msdb.dbo.backupset.database_name 
order by
msdb.dbo.backupset.database_name