Tag Archives: DatabaseSize

Database FileSize

This script shows how much space is used and how mutch allocated dispace is still avalible before it runs out of resources.

-- Show avalible space on data disk
SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, 
vs.logical_volume_name, CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],
CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [Available Size (GB)],  
CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Space Free %] 
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs OPTION (RECOMPILE)
GO

DROP TABLE IF EXISTS ##DBSIZE;
GO
CREATE TABLE ##dbsize (
	[ComputerName] [sql_variant] NULL,
	[InstanceName] [sql_variant] NOT NULL,
	[SqlInstance] [sql_variant] NULL,
	[DBName] [nvarchar](128) NULL,
	[FileName] [sysname] NOT NULL,
	[Filegroup] [sysname] NULL,
	[PhysicalName] [nvarchar](260) NULL,
	[FileType] [nvarchar](60) NULL,
	[UsedSpaceMB] [float] NULL,
	[FreeSpaceMB] [float] NULL,
	[FileSizeMB] [float] NULL,
	[PercentUsed] [float] NULL,
	[GrowthMB] [float] NULL,
	[GrowthType] [varchar](7) NOT NULL,
	[MaxSizeMB] [float] NULL,
	[SpaceBeforeAutoGrow] [float] NULL,
	[SpaceBeforeMax] [float] NULL,
	[PossibleAutoGrowthMB] [float] NULL,
	[UnusableSpaceMB] [float] NULL
) ON [PRIMARY]
GO
declare @db varchar(128) -- database name 
declare @sql nvarchar(max)
DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM MASTER.dbo.sysdatabases 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @db  

WHILE @@FETCH_STATUS = 0  
BEGIN  
	SET @sql = N'USE ['+@db+']; 
	 INSERT INTO ##dbsize
	 SELECT 
	 SERVERPROPERTY(''MachineName'') AS ComputerName
	,ISNULL(SERVERPROPERTY(''InstanceName''),''MSSQLSERVER'') AS InstanceName
	,SERVERPROPERTY(''ServerName'') AS SqlInstance
	,DB_NAME() AS DBName
	,f.name AS [FileName]
	,fg.name AS [Filegroup]
	,f.physical_name AS [PhysicalName]
	,f.type_desc AS [FileType]
	,CAST(CAST(FILEPROPERTY(f.name,''SpaceUsed'') AS INT)/128.0 AS FLOAT) AS [UsedSpaceMB]
	,CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS INT) / 128.0 AS FLOAT) AS [FreeSpaceMB]
	,CAST((f.size/128.0) AS FLOAT) AS [FileSizeMB]
	,CAST((FILEPROPERTY(f.name, ''SpaceUsed'')/(f.size / 1.0)) * 100 AS FLOAT) AS [PercentUsed]
	,CAST((f.growth/128.0) AS FLOAT) AS [GrowthMB]
	,CASE is_percent_growth
		WHEN 1
			THEN ''pct''
		WHEN 0
			THEN ''MB''
		ELSE ''Unknown''
	END AS [GrowthType]
	,CASE f.max_size
		WHEN - 1
			THEN 2147483648.
		ELSE CAST((f.max_size / 128.0) AS FLOAT)
	END AS [MaxSizeMB]
	,CAST((f.size / 128.0) AS FLOAT)-CAST(CAST(FILEPROPERTY(f.name,''SpaceUsed'')AS INT)/128.0AS FLOAT) AS [SpaceBeforeAutoGrow]
	,CASE f.max_size
		WHEN (- 1)
			THEN CAST(((2147483648.) - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS INT)) / 128.0 AS FLOAT)
		ELSE CAST((f.max_size - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS INT)) / 128.0 AS FLOAT)
	END AS [SpaceBeforeMax]
	,CASE f.growth
		WHEN 0
			THEN 0.00
		ELSE CASE f.is_percent_growth
			WHEN 0
				THEN CASE f.max_size
					WHEN (- 1)
					THEN CAST(((((2147483648.)-f.Size)/f.Growth)*f.Growth)/128.0 AS FLOAT)
					ELSE CAST((((f.max_size-f.Size)/f.Growth)*f.Growth)/128.0 AS FLOAT)
					END
			WHEN 1
				THEN CASE f.max_size
					WHEN (- 1)
					THEN CAST(CONVERT([int],f.Size*power((1)+CONVERT([float],f.Growth)/(100),CONVERT([int],log10(CONVERT([float],(2147483648.))/CONVERT([float],f.Size))/log10((1)+CONVERT([float],f.Growth)/(100)))))/128.0AS FLOAT)
					ELSE CAST(CONVERT([int],f.Size*power((1)+CONVERT([float],f.Growth)/(100),CONVERT([int],log10(CONVERT([float],f.Max_Size)/CONVERT([float],f.Size))/log10((1)+CONVERT([float],f.Growth)/(100)))))/128.0AS FLOAT)
				END
			ELSE (0)
		END
	END AS [PossibleAutoGrowthMB]
	,CASE f.max_size
		WHEN - 1
			THEN 0
		ELSE CASE f.growth
			WHEN 0
			THEN (f.max_size-f.size)/128
		ELSE CASE f.is_percent_growth
			WHEN 0
				THEN CAST((f.max_size-f.size-(CONVERT(FLOAT,FLOOR((f.max_size-f.Size)/f.Growth)*f.Growth)))/128.0 AS FLOAT)
			ELSE CAST((f.max_size-f.size-(CONVERT([int],f.Size*power((1)+CONVERT([float],f.Growth)/(100),CONVERT([int],log10(CONVERT([float],f.Max_Size)/CONVERT([float],f.Size))/log10((1)+CONVERT([float],f.Growth)/(100)))))))/128.0 AS FLOAT)
			END
		END
	END AS [UnusableSpaceMB]
	FROM sys.database_files AS f WITH (NOLOCK)
	LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)
		ON f.data_space_id = fg.data_space_id'

	EXEC (@SQL)
    FETCH NEXT FROM db_cursor INTO @db 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 


SELECT * FROM ##dbsize
DROP TABLE ##dbsize
GO