March 11, 2008 at 4:57 pm
Comments posted to this topic are about the item An easy way to track the growth of your database
April 16, 2008 at 4:47 am
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
May 7, 2008 at 5:42 am
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
May 7, 2008 at 7:39 am
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)
January 6, 2009 at 11:16 pm
to apply this script to every database on the server try using this and modify the script as appropriate:
February 8, 2009 at 11:07 am
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
February 20, 2009 at 1:15 pm
How would I modify this script to show the results in MB instead of GB?
March 27, 2009 at 11:40 am
use the figure 1048576 instead of 1024000000 or 1073741824.
KB = 1024
MB = 1024^2
GB = 1024^3
so on and so forth.
April 21, 2009 at 10:29 pm
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
April 21, 2009 at 10:32 pm
""""
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
June 8, 2009 at 7:38 am
Can the same thing be accomplished in Microsoft Access?
August 21, 2009 at 2:49 pm
nice script...i just added it to my DBA dashboard :-D...i already had another but this is better
September 14, 2009 at 11:45 am
How could we modify the code to include the growth of the log file?
May 20, 2011 at 7:07 am
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
November 1, 2013 at 11:10 am
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply