locked
SQL DB Growth RRS feed

  • Question

  • Hi ,

    As part of my job weekly i have to submit DB Growth for all the DB in all the server.

    Is there any T-SQL Script , which generate DB Growth in GB\MB\percentage. I having one script which generates reports based on Last Backup.

    Please suggest me good one.

     

     

    Wednesday, January 5, 2011 10:26 AM

Answers

  • You will have to create a table and once a day insert into current size of all dbs + date of inserting. Then you will be able to calculate growth + percentage based month,year amd etc

    Peter has written that script

    SELECT      @@SERVERNAME AS SqlServerInstance,
                db.name AS DatabaseName,
                SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE 8192.0E * af.size / 1048576.0E END) AS DatabaseSize,
                SUM(CASE WHEN af.groupid = 0 THEN 8192.0E * af.size / 1048576.0E ELSE 0 END) AS LogSize,
                SUM(8192.0E * af.size / 1048576.0E) AS TotalSize
    FROM        master..sysdatabases AS db
    INNER JOIN master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
    WHERE       db.name NOT IN('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb')    GROUP BY    db.name


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, January 5, 2011 10:43 AM
    Answerer