Database Growth Rate Script

I developed this script to give me the growth rates of any specified database. Plug in the database name and the date range (or leave date range blank for whole span of time available) and run. The database sizes are based of entries in msdb.dbo.backupset & msdb.dbo.backupfile...

SQL BELOW...

SET NOCOUNT on

--------------------------Declaring Variables------------------------------

DECLARE @dbname VARCHAR(50)

,@startsize INT

,@endsize INT

,@increase INT

,@datespan DECIMAL(10,2)

,@perday DECIMAL(10,2)

,@permonth DECIMAL(10,2)

,@peryear DECIMAL(10,2)

,@sql VARCHAR(MAX)

,@Database VARCHAR(50)

,@Startdate DATETIME

,@Enddate DATETIME

----------------------Specify Database and DateRange--------------------------

SET @Database = '' --Set Specific Database or leave blank for all Databases

SET @Startdate = '' SET @Enddate = '' --Set Date Range or Leave both blank (YYY-MM-DD)

----------------------------Declaring Variables-------------------------------

IF @Database <> ''

BEGIN

IF @Startdate = '' SET @Startdate = '1901-01-01'

IF @Enddate = '' SET @Enddate = '2999-01-01'

PRINT 'Growth rate based on historical data in msdb.dbo.backupfile Table...'

PRINT CHAR(13)

SET @startsize = (SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576)

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

AND TYPE = 'D'

AND backup_start_date between @startdate and @enddate

ORDER BY CONVERT(CHAR, backup_start_date, 111) ASC)

SET @endsize = (SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576)

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

AND TYPE = 'D'

AND backup_start_date between @startdate and @enddate

ORDER BY CONVERT(CHAR, backup_start_date, 111) DESC)

SET @datespan = (SELECT COUNT(DISTINCT( backup_start_date ))

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

AND TYPE = 'D'

AND backup_start_date between @startdate and @enddate)

SET @increase = @endsize - @startsize

SET @perday = @increase / @datespan

SET @permonth = @perday * 30

SET @peryear = @perday * 365

-------------------Retrieving Growth Stats For Specified DB------------------

SELECT TOP 1 CONVERT(VARCHAR(30), database_name)AS 'BackupFile.bak'

,(SELECT TOP 1 CONVERT(CHAR, backup_start_date, 111)

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

AND TYPE = 'D'

AND backup_start_date between @startdate and @enddate

ORDER BY CONVERT(CHAR, backup_start_date, 111) ASC) AS 'StartDate'

,(SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576)

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

AND TYPE = 'D'

AND backup_start_date between @startdate and @enddate

ORDER BY CONVERT(CHAR, backup_start_date, 111) ASC) AS 'StartSize_MB'

,(SELECT TOP 1 CONVERT(CHAR, backup_start_date, 111)

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

AND TYPE = 'D'

AND backup_start_date between @startdate and @enddate

ORDER BY CONVERT(CHAR, backup_start_date, 111) DESC) AS 'EndDate'

,(SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576)

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

AND TYPE = 'D'

AND backup_start_date between @startdate and @enddate

ORDER BY CONVERT(CHAR, backup_start_date, 111) DESC) AS 'EndSize_MB'

,(SELECT @datespan) AS 'DateSpan_Days'

,(SELECT @increase)AS 'Total_Increase_MB'

,(SELECT @perday)AS 'MB_Per_Day'

,(SELECT @permonth) AS 'MB_Per_Month'

,(SELECT @peryear)AS 'MB_Per_Year'

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

PRINT CHAR(13)

END

--------------------------DB Cycle Through for All DB--------------------------

IF @Database = ''

BEGIN

SELECT DISTINCT( database_name )

INTO #dbnametemp

FROM msdb.dbo.backupset

WHERE database_name <> 'msdb'

----------------------------Create Temp #AllDatabases-------------------------------

CREATE TABLE #AllDatabases

(

database_name VARCHAR(50)

,DateSpan_Days DECIMAL(10,2)

, Total_Increase_MB DECIMAL (10,2)

, MB_Per_Day DECIMAL(10,2)

, MB_Per_Month DECIMAL (10,2)

, MB_Per_Year DECIMAL (10,2)

)

-----------------------------------Cursor Start-------------------------------------

DECLARE db_curs CURSOR FOR

(SELECT database_name

FROM #dbnametemp)

OPEN db_curs

FETCH NEXT FROM db_curs INTO @database

WHILE @@FETCH_STATUS = 0

BEGIN

------------------------------------Define Variable-------------------------------------

IF @Startdate = '' SET @Startdate = '1901-01-01'

IF @Enddate = '' SET @Enddate = '2099-01-01'

SET @startsize = (SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576)

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

AND TYPE = 'D'

AND backup_start_date between @startdate and @enddate

ORDER BY CONVERT(CHAR, backup_start_date, 111) ASC)

SET @endsize = (SELECT TOP 1 CONVERT(INT, buf.backup_size/1048576)

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

AND TYPE = 'D'

AND backup_start_date between @startdate and @enddate

ORDER BY CONVERT(CHAR, backup_start_date, 111) DESC)

SET @datespan = (SELECT COUNT(DISTINCT( backup_start_date ))

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

AND TYPE = 'D'

AND backup_start_date between @startdate and @enddate)

SET @increase = @endsize - @startsize

SET @perday = @increase / @datespan

SET @permonth = @perday * 30

SET @peryear = @perday * 365

-----------------------------Insert Into #AllDatabases----------------------------------

INSERT INTO #AllDatabases

(

database_name

,DateSpan_Days

, Total_Increase_MB

, MB_Per_Day

, MB_Per_Month

, MB_Per_Year

)

SELECT TOP 1 CONVERT(VARCHAR(30), database_name)AS 'database_name'

,(SELECT @datespan) AS 'DateSpan_Days'

,(SELECT @increase)AS 'Total_Increase_MB'

,(SELECT @perday)AS 'MB_Per_Day'

,(SELECT @permonth) AS 'MB_Per_Month'

,(SELECT @peryear)AS 'MB_Per_Year'

FROM msdb.dbo.backupfile buf

JOIN msdb.dbo.backupset bus

ON bus.backup_set_id = buf.backup_set_id

WHERE database_name = @database

FETCH NEXT FROM db_curs INTO @database

END

CLOSE db_curs

DEALLOCATE db_curs

--------------------------Insert Totals in #AllDatabases-----------------------

INSERT INTO #AllDatabases

(

database_name

,DateSpan_Days

, Total_Increase_MB

, MB_Per_Day

, MB_Per_Month

, MB_Per_Year

)

SELECT

(Select 'TOTALDATA')

,(Select top 1 DateSpan_Days

FROM #AllDatabases)

,(Select SUM(Total_Increase_MB)

FROM #AllDatabases)

,(Select SUM(MB_Per_Day)

FROM #AllDatabases)

,(Select SUM(MB_Per_Month)

FROM #AllDatabases)

,(Select SUM(MB_Per_Year)

FROM #AllDatabases)

--------------------------Select On #AllDatabases-----------------------

PRINT 'Growth rate based on historical data in msdb.dbo.backupfile Table...'

PRINT CHAR(13)

SELECT

database_name as 'BackupFile.bak'

--,database_name

--,DateSpan_Days

--,Total_Increase_MB

,MB_Per_Day

,MB_Per_Month

,MB_Per_Year

FROM #AllDatabases

DROP TABLE #AllDatabases

DROP TABLE #dbnametemp

END