Database growth

Question

Database growth

Andre 425568

SSCrazy

Points: 2495
More actions
July 23, 2025 at 9:29 am

#4626053
Hi all
I am currently using the following script to store database growth at clients. There are 2 issues though.
1. One is that it is not taking into account free space on the database, so if there is as an example 100GB free space we do not see any growth until the 100GB is used and he MDF file does increase. I have not been able to find a column in the 2 tables referenced to resolve the issue. (I am not able the shrink the database for different reasons, main reason at this stage is the issues caused with fragmentation etc)
2. The other issue is that apparently sysaltfiles will be discontinued in a future release, maybe in SQL 202 already so I will need another way of getting the data.
Any assistance will be appreciated

SELECT      @@SERVERNAME AS SqlServerInstance,
db.name AS DatabaseName,
SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE 8192.0E * af.size / 1048576.0E END) AS DatabaseSize,
SUM(CASE WHEN af.groupid = 0 THEN 8192.0E * af.size / 1048576.0E ELSE 0 END) AS LogSize,
SUM(8192.0E * af.size / 1048576.0E) AS TotalSize,
CONVERT(VARCHAR(10), GETDATE(), 105) as MeasureDate
FROM        master..sysdatabases AS db
INNER JOIN master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
WHERE       db.name NOT IN('master', 'tempdb', 'model', 'msdb')       -- System databases

GROUP BY    db.name
GO

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113565 More actions · Answer 1

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = (
SELECT 
'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +


/*Put you per DB query in this segment*/
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'FileID = df.file_id, ' + CHAR(13) + CHAR(10) +
'LogicalName = df.name, ' + CHAR(13) + CHAR(10) +
'PhysicalName = df.physical_name, ' + CHAR(13) + CHAR(10) +
'FileSize_MB = CONVERT(DECIMAL(12, 2), ROUND(df.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'SpaceUsed_MB = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(df.NAME, ''SpaceUsed'') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'FreeSpace_MB = CONVERT(DECIMAL(12, 2), ROUND((df.size - fileproperty(df.NAME, ''SpaceUsed'')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'PercentageGrowth = CASE is_percent_growth WHEN 1 THEN ''Yes'' ELSE ''No'' END, ' + CHAR(13) + CHAR(10) +
'SizeOfNextGrowth_MB = CASE is_percent_growth WHEN 1 THEN ((CONVERT(DECIMAL(12, 2), ROUND(df.size / 128.000, 2)) /100) * df.growth) ELSE ((df.growth * 8.0)/1024.0) END ' + CHAR(13) + CHAR(10) +
'FROM sys.database_files df; '
/*End of per DB query*/


FROM sys.databases WHERE STATE_DESC = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')
--select @sql
EXECUTE sp_executesql @SQL

This should satisfy your requirements, FileSize, UsedFileSize, FreeFileSize for all online databases on an instance.

Andre 425568 SSCrazy Points: 2495 More actions · Answer 2

Thanks, I will test this. I will modify this as I want to export this to a table so we can track monthly growth