Post reply

database growth

  • July 14, 2010 at 12:17 am

    #157080

    Hi,

    For determining the growth of a database, which method is the good one.

    1. select 'dbname' as "dbname",SUM(size*8)/1024 "Sizein MB",getdate() as "Date" from dbnamedbo.sysfiles

    2. We have daily full backup scheduled, so i would go for

    SELECT a.database_name,

    a.type,

    CASE a.type WHEN 'D' THEN 'FULL BACKUP'

    WHEN 'I' THEN 'Differential Backup'

    WHEN 'L' THEN 'Transaction Log bkp'

    WHEN 'F' THEN 'File or Filegroup bkp'

    WHEN 'G' then 'Differential File bkp'

    WHEN 'P' THEN 'Partial bkp'

    WHEN 'Q' THEN 'Differential partial' END as backupType,

    a.backup_size/1024/1024 AS backupSizeMB,

    a.backup_finish_date as last_backup

    FROM msdb.dbo.backupset a

    inner join msdb.dbo.backupmediafamily m

    ON a.media_set_id = m.media_set_id

    WHERE a.database_name = 'dbname'

    and a.type = 'D'

    order by a.backup_finish_date

    I personally beleive going for secondnd method is reasonable. I can clearly observe the change in MB of actual data.

    I have seen, few are using way1 as well in there environments

    But, i wana know which is good??

    Thanks in Advance

  • July 14, 2010 at 12:22 am

    #1192571

    Option 2 is probably the most common. It is pretty reliable and you may already have access to the history of the backups allowing you to pull a trend now.

    Option 1 would need to be scheduled and stored in a database somewhere so you could trend it at a later time.

    Either option should work - depends on implementation and planning involved with the solution chosen.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 14, 2010 at 3:07 am

    #1192632

    Thanks 🙂

  • July 14, 2010 at 9:58 am

    #1192939

    You're welcome

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 4 posts - 1 through 3 (of 3 total)

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