Effective capacity planning is a critical task for DBAs responsible for managing large SQL Server databases. As data volumes continue to grow at an exponential rate, it’s essential to monitor database size and usage trends to ensure optimal performance and prevent unexpected downtime. That’s where a T-SQL script like this one can be a valuable tool for monitoring the size and usage trends of large SQL Server databases. DBAs can gain insights into where storage capacity may be reaching its limits and take proactive steps to optimize their database environment. Whether preparing for future growth or addressing immediate capacity constraints, this script can help DBAs stay ahead of the curve and keep their databases running smoothly.
---------------------------------------------------------------------------------
-- Script to get the sql server database size monthly growth using the backupset
---------------------------------------------------------------------------------
--SECTION 1 BEGIN
WITH BackupsSize AS(
SELECT TOP 15
ROW_NUMBER() OVER (ORDER BY DATEPART(year,[backup_start_date]) ASC, DATEPART(month,[backup_start_date]) ASC) AS [rn]
, DATEPART(year,[backup_start_date]) AS [Year]
, DATEPART(month,[backup_start_date]) AS [Month]
, CONVERT(DECIMAL(10,2),ROUND(AVG([backup_size]/1024/1024/1024),4)) AS [Backup Size GB]
, CONVERT(DECIMAL(10,2),ROUND(AVG([compressed_backup_size]/1024/1024/1024),4)) AS [Compressed Backup Size GB]
FROM
msdb.dbo.backupset
WHERE
[database_name] = 'DbName here' -- type your database name here
AND [type] = 'D'
AND backup_start_date BETWEEN DATEADD(mm, - 12, GETDATE()) AND GETDATE()
GROUP BY
[database_name]
, DATEPART(yyyy, [backup_start_date])
, DATEPART(mm, [backup_start_date])
ORDER BY
[Year], [Month]
)
--SECTION 1 END
--SECTION 2 BEGIN
SELECT
b.Year
, b.Month
, b.[Backup Size GB]
, 0 AS deltaNormal
, b.[Compressed Backup Size GB]
, 0 AS deltaCompressed
FROM
BackupsSize b
WHERE
b.rn = 1
UNION
SELECT
b.Year
, b.Month
, b.[Backup Size GB]
, b.[Backup Size GB] - d.[Backup Size GB] AS deltaNormal -- size difference (current month - previous month)
, b.[Compressed Backup Size GB]
, b.[Compressed Backup Size GB] - d.[Compressed Backup Size GB] AS deltaCompressed -- size difference (current month - previous month)
FROM
BackupsSize b
CROSS APPLY (
SELECT bs.[Backup Size GB], bs.[Compressed Backup Size GB]
FROM BackupsSize bs
WHERE bs.rn = b.rn - 1
) AS d
--SECTION 2 END
Source: mssqltips
