An easy way to track the growth of your database

  • Comments posted to this topic are about the item An easy way to track the growth of your database

  • hi,

    I've just implented the script and think the result output is awesome.

    However i was wondering if there was any way within the script that it would include all databases on the server instead of one at at time, and to have a history on the size since it was first created. Also is there any way it could be executed as a job, if so what do you reckon the command will be, to run this in SQL AGENT agent

    ie something similar to

    exec sp_track_db_growth "northwind" s an example

    Like i said the script was and is fantabulous!!!!!!!!!!!!!!!.

    CIAO

    vivcolli:D

  • Nice script, except to convert bytes to gigabytes you need to divide it by 1073741824 Bytes

    See http://www.123marbella.net/en/free-bandwith-calculator.html

    David Bird

  • Good article. I just found that using the floor function gave the size as 1 right through because the db that I work on is 1.6 gb so I rather used the round function and it came up tops. Thanks for a good article.

    Below is an example of what I did.

    select BackupDate = convert(varchar(10),backup_start_date, 111), SizeInGigs=round(backup_size/1024000000,4)

    from msdb..backupset

    where database_name = 'OrisysSql' and type = 'd'

    order by backup_start_date desc

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • to apply this script to every database on the server try using this and modify the script as appropriate:

    http://www.sqlservercentral.com/scripts/Metadata/65453/

  • what about the databases which are backed up with compression method like litespeed, let say today backup is taken with native method and tommorrow backup is taken with litespeed, how do we know whther databae size is increased or not.

    thanks

    Joseph

  • How would I modify this script to show the results in MB instead of GB?

  • use the figure 1048576 instead of 1024000000 or 1073741824.

    KB = 1024

    MB = 1024^2

    GB = 1024^3

    so on and so forth.

  • Greate Script. Usually peopel would like to see percentage growth which is easily calulate using this script of yours. Providing the Db always gorwos in size thsi will work. - Script great Idee 😎

    Select ((MAX(SizeInGigs) - MIN(SizeInGigs))/ MAX(SizeInGigs))*100 from (

    select BackupDate = convert(varchar(10),backup_start_date, 111)

    ,SizeInGigs=floor(backup_size)

    from msdb..backupset

    where

    database_name = 'CES'

    and type = 'd'

    )TB

  • """"

    what about the databases which are backed up with compression method like litespeed, let say today backup is taken with native method and tommorrow backup is taken with litespeed, how do we know """"

    If you use percentage growth - over the time periods not compressed and compressed periods it should be more or less the same

  • Can the same thing be accomplished in Microsoft Access?

  • nice script...i just added it to my DBA dashboard :-D...i already had another but this is better

  • How could we modify the code to include the growth of the log file?

  • In the interim you could:

    select BackupDate = convert(varchar(10),backup_start_date, 111), SizeInGigs=

    CAST(round(backup_size/1073741824,4) AS decimal(18,4)),

    backup_size AS [Raw backup_size],

    CASE TYPE

    WHEN 'D' THEN 'Full'

    WHEN 'F' THEN 'Filegroup'

    WHEN 'I' THEN 'Differential'

    WHEN 'L' THEN 'Log'

    ELSE 'Unknown'

    END AS Type

    from msdb..backupset

    where database_name = 'ROOMBOOKING' --and type = 'D'

    order by backup_start_date DESC

  • That is a nice little query. Here's my quick re-write to show all databases and track percent growth since the first backup on record.

    SELECT

    'database_name' = mdbus.database_name

    ,'backup_start' = mdbus.backup_start_date

    ,'duration' = CASE

    WHEN (datediff([second], mdbus.backup_start_date, mdbus.backup_finish_date) / 60 / 60) >= 100 THEN '100+ hours'

    ELSE isnull(

    right('0' + cast( (datediff([millisecond], mdbus.backup_start_date, mdbus.backup_finish_date) / 1000 / 60 / 60) AS varchar(10)), 2)

    + ':' + right('0' + cast(((datediff([millisecond], mdbus.backup_start_date, mdbus.backup_finish_date) / 1000 / 60) % 60) AS varchar(10)), 2)

    + ':' + right('0' + cast(((datediff([millisecond], mdbus.backup_start_date, mdbus.backup_finish_date) / 1000) % 60) AS varchar(10)), 2)

    + '.' + right('00' + cast( (datediff([millisecond], mdbus.backup_start_date, mdbus.backup_finish_date) % 1000) AS varchar(10)), 3)

    , '')

    END

    ,'size_gb' = convert(decimal(10,2), (convert(float, mdbus.backup_size) / 1024 / 1024 / 1024))

    ,'percent_growth' = convert(decimal(10,2), (((convert(float, mdbus.backup_size) - mdbus_first.backup_size) / mdbus_first.backup_size) * 100))

    FROM

    msdb.dbo.backupset mdbus

    INNER JOIN

    (

    SELECT

    database_name

    ,'backup_set_id' = min(backup_set_id)

    FROM

    msdb.dbo.backupset

    GROUP BY

    database_name

    ) mdbus_first_rec ON (mdbus.database_name = mdbus_first_rec.database_name)

    INNER JOIN

    (

    SELECT

    database_name

    ,backup_set_id

    ,backup_start_date

    ,backup_size

    FROM

    msdb.dbo.backupset

    ) mdbus_first ON (mdbus.database_name = mdbus_first.database_name AND mdbus_first_rec.backup_set_id = mdbus_first.backup_set_id)

    WHERE

    mdbus.[type] = 'd'

    ORDER BY

    mdbus.database_name

    ,mdbus.backup_start_date DESC

Viewing 15 posts - 1 through 15 (of 15 total)

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