![\"\"](\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/database-growth.png\")
<\/p>\nHi,<\/p>\n
Managers and customers are asking frequently what is the database size growth.<\/p>\n
<\/p>\n
<\/p>\n\n
<\/p>\n
Database size growth grafic is very important for the new storage order. Because Enterprise companies want to purchase new storage in one or two years not every 2-3 month.<\/p>\n
<\/p>\n
You can\u00a0analyse the database size growth using backup history with following script.<\/p>\n
DECLARE @startDate datetime;\r\nSET @startDate = GetDate();\r\n\r\nSELECT PVT.DatabaseName\r\n, PVT.[0], PVT.[-1], PVT.[-2], PVT.[-3], PVT.[-4], PVT.[-5], PVT.[-6]\r\n, PVT.[-7], PVT.[-8], PVT.[-9], PVT.[-10], PVT.[-11], PVT.[-12]\r\nFROM\r\n(SELECT BS.database_name AS DatabaseName\r\n,DATEDIFF(mm, @startDate, BS.backup_start_date) AS MonthsAgo\r\n,CONVERT(numeric(10, 1), AVG(BF.file_size \/ 1048576.0)) AS AvgSizeMB\r\nFROM msdb.dbo.backupset as BS\r\nINNER JOIN\r\nmsdb.dbo.backupfile AS BF\r\nON BS.backup_set_id = BF.backup_set_id\r\nWHERE NOT BS.database_name IN\r\n('master', 'msdb', 'model', 'tempdb')\r\nAND BF.[file_type] = 'D'\r\nAND BS.backup_start_date BETWEEN DATEADD(yy, -1, @startDate) AND @startDate\r\nGROUP BY BS.database_name\r\n,DATEDIFF(mm, @startDate, BS.backup_start_date)\r\n) AS BCKSTAT\r\nPIVOT (SUM(BCKSTAT.AvgSizeMB)\r\nFOR BCKSTAT.MonthsAgo IN ([0], [-1], [-2], [-3], [-4], [-5], [-6], [-7], [-8], [-9], [-10], [-11], [-12])\r\n) AS PVT\r\nORDER BY PVT.DatabaseName;\r\n\r\n<\/pre>\n <\/p>\n
Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n