Post reply

Help with trending db growth

  • February 17, 2009 at 10:00 am

    #200282

    I have a script that runs nightly that shows that size of each database on the server. I would like to create a chart to trend the growth of each database over time. How can I show this? Every time I try it in excel (to make a pretty chart for Mgmt) it wants to sum up all the DB size values. Ideally I would break it up into monthly/yearly chunks to show trends on growth. Any ideas?

    Example:

    Date Server DB_Name Size in MB

    9/4/08 MSPDB04 LanDesk 3534

    9/5/08 MSPDB04 LanDesk 4383

    9/6/08 MSPDB04 LanDesk 4333

    9/7/08 MSPDB04 LanDesk 3463

    9/8/08 MSPDB04 LanDesk 4333

  • February 17, 2009 at 1:27 pm

    #945440

    Hi,

    Have you tried using a pivot chart or pivot table? These are pretty useful tools in Excel for mashing up your data.

    B

  • February 17, 2009 at 2:00 pm

    #945488

    yeah I tried that but I can't figure out how to do it without it summing the db size field

  • February 17, 2009 at 2:25 pm

    #945516

    Hmmm. true. It might not meet your needs. I guess it depends how you want to see the data.

    You can change it from SUMMING though... Right click on the pivot data table on the column "Sum of Size in MB" (or whatever it is called for you) and there is an option for "Summarise data by" you could change it to Average and then see how average size is changing by month or something?

  • August 24, 2012 at 11:46 pm

    #1530028

    HI

    FIRST OF ALL LET U KNOW ABOUT THIS SCRIPT IS FOR MONTHLY ANALYSIS

    SO U DO UR NEED FULL ACCORDINGLY

    Fisrt u run the below any of the script depens upon ur requirement and select in output window in ssms from ex--- 1/1/2011 to 1/1/2012 and calulate the growth individualy for every month present month date 31/3/2012 minus previous month 30/4/2012 tn u'vl get the growth of previous months same follow for daily accodring to ur requirement

    script 1:

    use msdb

    select DATABASE_NAME,backup_size,backup_start_date,

    backup_finish_date,database_creation_date

    from backupset where type='D'and

    database_name='Databasename'order by backup_finish_date desc //( REMOVE THIS "[DESC]"IF UWANT ASCENDING ORDER)

    script 2:

    use msdb

    select DATABASE_NAME,backup_size,

    backup_finish_date

    from backupset where type='D'and

    database_name='Databasename'order by backup_finish_date desc //( REMOVE THIS "[DESC]"IF UWANT ASCENDING ORDER)

    and check the Attachment and prepare ur with own credentials I mentions some dummy details of understanding

    Thanks & regards

    Naga.Rohit

    Thanks
    Naga.Rohitkumar

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

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