Database Growth History

  • Hi Greg,

    The instance on which it is failing has the version: Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

    But there is another instance with the version: Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) and it is running successfully on this.

  • hi Nicholas,

    I've found a problem when sum(Size) is too big to fit into an integer.

    Here is the fix:

    Best regards,

    Henrik

    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

    */

    CREATE TABLE ##tbl_DataSize

    (

    SizeDECIMAL(20)

    )

    CREATE TABLE #tbl_GrowthData

    (

    DatabaseNameVARCHAR(50)

    ,NoSampleDaysDECIMAL(20,3)

    ,DataSizeMBDECIMAL(20,3)

    ,LogSizeMBDECIMAL(20,3)

    ,BackupSizeMBDECIMAL(20,3)

    ,TotalSpaceMBDECIMAL(20,3)

    ,DataGrowthDECIMAL(20,3)

    ,LogGrowthDECIMAL(20,3)

    ,GrowthPercentageDECIMAL(20,3)

    )

    DECLARE

    @iNoSamplesbigINT

    ,@nMaxBackupSizeDECIMAL

    ,@nMinBackupSizeDECIMAL

    ,@nMaxLogSizeDECIMAL

    ,@nMinLogSizeDECIMAL

    ,@nMaxDataSizeDECIMAL

    ,@nMinDataSizeDECIMAL

    ,@vcDatabaseNameVARCHAR(50)

    ,@dtMaxBackupTimeDATETIME

    ,@dtMinBackupTimeDATETIME

    ,@iMinBackupIDbigINT

    ,@iMaxBackupIDbigINT

    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.backupfileWHERE backup_set_id = @iMaxBackupID AND file_type = 'L')

    SET @nMinLogSize= (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfileWHERE backup_set_id = @iMinBackupID AND file_type = 'L')

    SET @nMaxDataSize= (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfileWHERE backup_set_id = @iMaxBackupID AND file_type = 'D')

    SET @nMinDataSize= (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfileWHERE backup_set_id = @iMinBackupID AND file_type = 'D')

    EXEC ('

    INSERT INTO ##tbl_DataSize

    SELECT CAST((SUM(cast(size as bigint))) 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

    ,@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

  • Nice script -thanks for sharing.

    I get multple errors when I run it though:

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near 'Service_1d756ce176df40ff88162ccaa'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    Msg 208, Level 16, State 1, Line 2

    Invalid object name 'Search_Service_Application_CrawlStoreDB_50d2dd819c.dbo.sysfiles'.

    Msg 208, Level 16, State 1, Line 2

    Invalid object name 'Search_Service_Application_DB_78718af6ae114220b5fb.dbo.sysfiles'.

    Msg 208, Level 16, State 1, Line 2

    Invalid object name 'Search_Service_Application_PropertyStoreDB_6c687a8.dbo.sysfiles'.

    Msg 208, Level 16, State 1, Line 2

    Invalid object name 'Secure_Store_Service_DB_9ea28dcf81de4bfb857b8121a0.dbo.sysfiles'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

    Regards,

    Snorri

  • The script database name is defined for 50 chars. Your db names are longer so you will need to increase it. You may also have to enclose the DB name in brackets.

    ,@vcDatabaseNameVARCHAR(50)

  • wakanni,

    If you're getting an error it might be due to having a space in the database name. Add some brackets in the code below around the database parameter value. I hope this helps.

    EXEC ('

    INSERT INTO ##tbl_DataSize

    SELECT CAST((SUM(size)) as DECIMAL(20,3)) FROM ['+@vcDatabaseName+'].dbo.sysfiles')

  • I had a syntax error also; a database name on my system is a reserved word. Fix the issue by surrounding the database name in the dynamic sql with square brackets.

    EXEC ('

    INSERT INTO ##tbl_DataSize

    SELECT CAST((SUM(size)) as DECIMAL(20,3)) FROM ['+@vcDatabaseName+'].dbo.sysfiles'

    )

  • Thank you - problem solved.

    (Needed also brackets for DB name: '.....FROM ['+@vcDatabaseName+'].dbo.sysfiles')

  • Very nice script. Thanks.

  • Very nice script.

    I noticed that some of those ungodly long-named SharePoint databases were getting their names chopped off so I changed the variable and the #temp table column names to VARCHAR(128)

    CREATE TABLE #tbl_GrowthData

    (

    DatabaseNameVARCHAR(128)

    ...

    ...

    DECLARE

    ...

    ,@vcDatabaseNameVARCHAR(128)

    G. Milner

  • Hi,

    Absolutely this is a nice script.

    i have a small doubt in this. Please assist.

    .. Is it possible to see the file wise growth of the each DB ?.

    So that we can confidently say that So and so file in the particular disk has grown this much %.

    Thanks,

    Arjun

  • Thanks for sharing. Cool script.

    You also might want to limit the data collection for only 'ONLINE' databases.

    SELECT [name] FROM master.dbo.sysdatabases

    WHERE DATABASEPROPERTYEX(name, 'status') = 'ONLINE'

    ORDER BY [name]

    OPEN file_cursor

    ~Leon

  • A nice script.

    2 cents

    If you run this script against SharepPoint database, please increase the following two variables.

    1. DatabaseNameVARCHAR(250)

    2. ,@vcDatabaseNameVARCHAR(250)

    Thanks

  • getting below error

    Msg 8115, Level 16, State 2, Line 2

    Arithmetic overflow error converting expression to data type int.

    The statement has been terminated.

Viewing 13 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply