SET NOCOUNT ON /* Author: Nicholas Williams Date: 3rd February 2008 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: Nicholas.Williams@reagola.com
Tested On: SQL 2000 SQL 2005 SQL 2008 SQL 2008R2 */
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 master.dbo.sysdatabases 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 msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' ) SET @dtMinBackupTime = ( SELECT MIN(backup_finish_date) FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' ) SET @iNoSamples = DATEDIFF ( dd ,@dtMinBackupTime ,@dtMaxBackupTime )
SET @nMaxBackupSize = ( SELECT backup_size FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMaxBackupTime ) SET @nMinBackupSize = ( SELECT backup_size FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMinBackupTime )
SET @iMaxBackupID = ( SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMaxBackupTime ) SET @iMinBackupID = ( SELECT MAX(backup_set_id) FROM msdb.dbo.backupset 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 msdb.dbo.backupfile WHERE backup_set_id = @iMaxBackupID AND file_type = 'L' ) SET @nMinLogSize = ( SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile WHERE backup_set_id = @iMinBackupID AND file_type = 'L' ) SET @nMaxDataSize = ( SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile WHERE backup_set_id = @iMaxBackupID AND file_type = 'D' ) SET @nMinDataSize = ( SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile WHERE backup_set_id = @iMinBackupID AND file_type = 'D' )
EXEC ('INSERT INTO ##tbl_DataSize SELECT CAST((SUM(size)) as DECIMAL(20,3)) FROM '+@vcDatabaseName+'.dbo.sysfiles')
INSERT INTO #tbl_GrowthData SELECT @vcDatabaseName DatabaseName ,@iNoSamples NoSampleDays ,@nMaxDataSize ,@nMaxLogSize ,@nMaxBackupSize / 1048576 ,((size * 8192) / 1048576) TotalSpaceUsed ,@nMaxDataSize - @nMinDataSize ,@nMaxLoggSize - @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
|