Cool Tips‎ > ‎Backup and Restore‎ > ‎

Backup: Get Database File Growth

 

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author: Nicholas Williams
Date:    3 Feb 2008

Description

Calculates Growth Info for all databases on a server that are being backed up. Extracts file sizes from the backup history in the msdb backup tables, and as a result will only contain growth data for databases that are being backed up. If a database is not being backup up the results will be NULL. (For example the Tempdb).
The growth figures produced here do not reflect any autogrowth events. 

Code

 

 
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

 
 
ċ
Get Database Growth.sql
(4k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments