SET NOCOUNT ON
/*
Desc: Calculates Growth Info for all databases on a server that are being
backed up. Relies on the backup tables, and as a result will only contain as many
days history as do the backup tables(@iNoSamples). If a database is
not being backup up the results will be NULL. (For example the Tempdb)
This is a rewrite of something I did a few years ago, as I dont know
where I saved the other code. bummer.
Email: [Link]@[Link]
*/
CREATE TABLE ##tbl_DataSize
(
Size DECIMAL(20)
)
CREATE TABLE #tbl_GrowthData
(
DatabaseName VARCHAR(50)
,NoSampleDays DECIMAL(20,3)
,DataSizeMB DECIMAL(20,3)
,LogSizeMB DECIMAL(20,3)
,BackupSizeMB DECIMAL(20,3)
,TotalSpaceMB DECIMAL(20,3)
,DataGrowth DECIMAL(20,3)
,LogGrowth DECIMAL(20,3)
,GrowthPercentage DECIMAL(20,3)
)
DECLARE
@iNoSamples INT
,@nMaxBackupSize DECIMAL
,@nMinBackupSize DECIMAL
,@nMaxLogSize DECIMAL
,@nMinLogSize DECIMAL
,@nMaxDataSize DECIMAL
,@nMinDataSize DECIMAL
,@vcDatabaseName VARCHAR(50)
,@dtMaxBackupTime DATETIME
,@dtMinBackupTime DATETIME
,@iMinBackupID INT
,@iMaxBackupID INT
DECLARE file_cursor CURSOR FOR
SELECT [name] FROM [Link]
ORDER BY [name]
OPEN file_cursor
FETCH NEXT FROM file_cursor
INTO @vcDatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dtMaxBackupTime = (SELECT MAX(backup_finish_date)FROM [Link] WHERE
database_name = @vcDatabaseName AND [type] = 'D')
SET @dtMinBackupTime = (SELECT MIN(backup_finish_date)FROM [Link] WHERE
database_name = @vcDatabaseName AND [type] = 'D')
SET @iNoSamples =
DATEDIFF
(
dd
,@dtMinBackupTime
,@dtMaxBackupTime
)
SET @nMaxBackupSize = (SELECT backup_size FROM [Link] WHERE
database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date =
@dtMaxBackupTime)
SET @nMinBackupSize = (SELECT backup_size FROM [Link] WHERE
database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date =
@dtMinBackupTime)
SET @iMaxBackupID = (SELECT MAX(backup_set_id) FROM [Link] WHERE
database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date =
@dtMaxBackupTime)
SET @iMinBackupID = (SELECT MAX(backup_set_id) FROM [Link] WHERE
database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date =
@dtMinBackupTime)
SET @nMaxLogSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) /
1048576) FROM [Link] WHERE backup_set_id = @iMaxBackupID AND
file_type = 'L')
SET @nMinLogSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) /
1048576) FROM [Link] WHERE backup_set_id = @iMinBackupID AND
file_type = 'L')
SET @nMaxDataSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) /
1048576) FROM [Link] WHERE backup_set_id = @iMaxBackupID AND
file_type = 'D')
SET @nMinDataSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) /
1048576) FROM [Link] WHERE backup_set_id = @iMinBackupID AND
file_type = 'D')
EXEC ('
INSERT INTO ##tbl_DataSize
SELECT CAST((SUM(size)) as DECIMAL(20,3)) FROM '+@vcDatabaseName+'.[Link]'
)
INSERT INTO #tbl_GrowthData
SELECT
@vcDatabaseName DatabaseName
,@iNoSamples NoSampleDays
,@nMaxDataSize
,@nMaxLogSize
,@nMaxBackupSize / 1048576
,((size * 8192) / 1048576) TotalSpaceUsed
,@nMaxDataSize - @nMinDataSize
,@nMaxLogSize - @nMinLogSize
,(((@nMaxDataSize + @nMaxLogSize) - (@nMinDataSize+ @nMinLogSize)) /
(@nMinDataSize+ @nMinLogSize)) * 100.00
--growth percentage is calculated based upon the original data size, before
the growth. as a result it may look a little funny, but it is accurate. or at least
I think so :)
FROM ##tbl_DataSize
TRUNCATE TABLE ##tbl_DataSize
FETCH NEXT FROM file_cursor
INTO @vcDatabaseName
END
CLOSE file_cursor
DEALLOCATE file_cursor
SELECT
*
FROM #tbl_GrowthData
DROP TABLE ##tbl_DataSize
DROP TABLE #tbl_GrowthData
SET NOCOUNT OFF