February 17, 2009 at 10:00 am
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
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
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
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
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