Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

An easy way to track the growth of your database Expand / Collapse
Author
Message
Posted Monday, June 8, 2009 7:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 18, 2013 10:37 AM
Points: 75, Visits: 83
Can the same thing be accomplished in Microsoft Access?
Post #730671
Posted Friday, August 21, 2009 2:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 3, 2014 3:07 PM
Points: 221, Visits: 284
nice script...i just added it to my DBA dashboard ...i already had another but this is better
Post #775438
Posted Monday, September 14, 2009 11:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 9:06 PM
Points: 288, Visits: 815
How could we modify the code to include the growth of the log file?
Post #787643
Posted Friday, May 20, 2011 7:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 13, 2014 7:49 AM
Points: 203, Visits: 623
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
Post #1112372
Posted Friday, November 1, 2013 11:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 4:06 PM
Points: 450, Visits: 466
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

Post #1510721
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse