December 16, 2011 at 6:01 am
Hi all,
I want to take report of database growth day by day. Please let me know if there is any way to find out database growth rate daily basis. I took report by msdb.dbo.backupset but I is not helping my purpose.
Thanks in advance.
December 16, 2011 at 6:06 am
You data growth in the datafiles or the data files themselves growth?
December 16, 2011 at 6:14 am
Ninja-Its data growth in data files.
December 16, 2011 at 6:17 am
The backup size history is perfect for that. You just have to not delete it!
Here's a beta script I built :
IF OBJECT_ID('tempdb..#dbs') > 0
DROP TABLE #dbs
IF OBJECT_ID('tempdb..#Drives') > 0
DROP TABLE #Drives
IF OBJECT_ID('tempdb..#Results') > 0
DROP TABLE #Results
CREATE TABLE #dbs
(
DBNAME sysname
, DBID INT
, [Total Size in MB] INT
, [Available Space In MB] INT
, DriveLetter CHAR(1)
)
INSERT INTO
#dbs
(
DBNAME
, DBID
, [Total Size in MB]
, [Available Space In MB]
, DriveLetter
)
EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() As DBNAME
, DB_ID() AS DBID
, SUM(size / 128) AS ''Total Size in MB''
, SUM(size / 128 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128) AS ''Available Space In MB''
, LEFT(physical_name, 1) AS DriveLetter
FROM
[?].sys.database_files
WHERE
type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1) '
CREATE TABLE #Drives
(
DriverLetter CHAR(1) PRIMARY KEY CLUSTERED
, FreeMBs INT NOT NULL
, FreeGBs AS CONVERT(DECIMAL(18 , 2) , FreeMBs / 1024.0)
)
INSERT INTO
#Drives ( DriverLetter , FreeMBs )
EXEC xp_fixeddrives
--
--SELECT
-- DB_NAME() As DBNAME
-- , DB_ID() AS DBID
-- , SUM(size / 128) AS 'Total Size in MB'
-- , SUM(size / 128 - CAST(FILEPROPERTY(name , 'SpaceUsed') AS int) / 128) AS 'Available Space In MB'
--FROM
-- sys.database_files
--WHERE
-- type_desc = 'ROWS'
--Rémi : I deleted 4 logging tables I had build on March 25th, hence the ±350 MB drop.
;
WITH CTE_Backups ( database_name, BackupDate, MinutesForBackup, GB_backup_size, seqFirst, seqLast )
AS (
SELECT
bs.database_name
, DATEADD(D , 0 , DATEDIFF(D , 0 , bs.backup_start_date)) AS BackupDate
, CONVERT(DECIMAL(18 , 1) , DATEDIFF(s , bs.backup_start_date ,
bs.backup_finish_date)
/ 60.0) AS MinutesForBackup
, CONVERT(DECIMAL(18 , 3) , bs.backup_size / 1024 / 1024 / 1024) AS GB_backup_size
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date ) AS seqFirst
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ) AS seqLast
FROM
msdb.dbo.backupset bs
WHERE
bs.[type] = 'D'
AND bs.is_snapshot = 0 --veem agent that backups the whole server
-- AND bs.database_name NOT LIKE '%ate%'
-- AND name IS NULL
)
SELECT
CONVERT(INT , dtBackups.[Available Space In GB]
/ CASE WHEN dtBackups.GB_ExpectedDailyGrowth <> 0
THEN dtBackups.GB_ExpectedDailyGrowth
ELSE 0.0001
END) AS DaysUntillDBGrowth
, *
-- INTO
-- #Results
FROM
(
SELECT
a.database_name
, dbs.DriveLetter
, drv.FreeGBs AS FreeGBs_Drive
, CONVERT(DECIMAL(18 , 3) , drv.FreeGBs * 0.85
/ NULLIF(b.GB_backup_size - a.GB_backup_size, 0)
/ NULLIF(DATEDIFF(dd , a.BackupDate , b.BackupDate), 0) * 30.468) AS FreeGBs_Drive_InMonths_WithExpected_DB_Growth
, a.BackupDate AS BackupDate_First
, b.BackupDate AS BackupDate_Last
, DATEDIFF(dd , a.BackupDate , b.BackupDate) AS DaysPeriod
, a.MinutesForBackup AS MinutesForBackup_First
, b.MinutesForBackup AS MinutesForBackup_Last
, b.MinutesForBackup - a.MinutesForBackup AS MinutesForBackup_Delta
, a.GB_backup_size AS GB_backup_size_First
, b.GB_backup_size AS GB_backup_size_Last
, b.GB_backup_size - a.GB_backup_size AS GB_BackupGrowth
--, a.seqLast - a.seqFirst AS QtyofBackups
, CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ NULLIF(DATEDIFF(dd , a.BackupDate , b.BackupDate), 0)) AS GB_ExpectedDailyGrowth
, CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ NULLIF(DATEDIFF(dd , a.BackupDate , b.BackupDate), 0) * 365.256) AS GB_ExpectedAnnualGrowth
, CONVERT(DECIMAL(18 , 3) , dbs.[Total Size in MB] / 1024.0) AS [Total Size in GB]
, CONVERT(DECIMAL(18 , 3) , dbs.[Available Space In MB] / 1024.0) AS [Available Space In GB]
FROM
CTE_Backups a
INNER JOIN CTE_Backups b
ON a.seqFirst = b.seqLast
AND a.seqLast = b.seqFirst
AND a.database_name = b.database_name
INNER JOIN #dbs dbs
ON b.database_name = dbs.DBNAME
LEFT OUTER JOIN #Drives drv
ON dbs.DriveLetter = drv.DriverLetter
WHERE
a.seqFirst = 1
AND a.seqFirst + a.seqLast > 2 --would always warn on the first day
) dtBackups
ORDER BY
database_name
--IF EXISTS ( SELECT
-- *
-- FROM
-- #Results R
-- WHERE
-- R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth >= 0 AND R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth < 1
-- OR R.DaysUntillDBGrowth >= 0 AND R.DaysUntillDBGrowth <= 30 )
-- BEGIN
-- INSERT INTO
-- dbo.RPT_Space_Warnings
-- (
-- [DaysUntillDBGrowth]
-- , [Warning_Description]
-- , [database_name]
-- , [DriveLetter]
-- , [FreeGBs_Drive]
-- , [FreeGBs_Drive_InMonths_WithExpected_DB_Growth]
-- , [BackupDate_First]
-- , [BackupDate_Last]
-- , [DaysPeriod]
-- , [MinutesForBackup_First]
-- , [MinutesForBackup_Last]
-- , [MinutesForBackup_Delta]
-- , [GB_backup_size_First]
-- , [GB_backup_size_Last]
-- , [GB_BackupGrowth]
-- , [GB_ExpectedDailyGrowth]
-- , [GB_ExpectedAnnualGrowth]
-- , [Total Size in GB]
-- , [Available Space In GB]
-- )
-- SELECT
-- [DaysUntillDBGrowth]
-- , CASE WHEN R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth < 1 THEN 'HD IS FULL' ELSE 'AUTOGROWTH WARNING' END AS Warning_Description
-- , [database_name]
-- , [DriveLetter]
-- , [FreeGBs_Drive]
-- , [FreeGBs_Drive_InMonths_WithExpected_DB_Growth]
-- , [BackupDate_First]
-- , [BackupDate_Last]
-- , [DaysPeriod]
-- , [MinutesForBackup_First]
-- , [MinutesForBackup_Last]
-- , [MinutesForBackup_Delta]
-- , [GB_backup_size_First]
-- , [GB_backup_size_Last]
-- , [GB_BackupGrowth]
-- , [GB_ExpectedDailyGrowth]
-- , [GB_ExpectedAnnualGrowth]
-- , [Total Size in GB]
-- , [Available Space In GB]
-- FROM
-- #Results R
-- WHERE
-- R.DaysUntillDBGrowth >= 0 AND R.DaysUntillDBGrowth <= 30
--UNION ALL -- I want to see 2 warnings in the same day when it's the case... those are the really critical events.
-- SELECT
-- [DaysUntillDBGrowth]
-- , CASE WHEN R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth < 1 THEN 'HD IS FULL_' ELSE 'AUTOGROWTH WARNING_' END AS Warning_Description
-- , [database_name]
-- , [DriveLetter]
-- , [FreeGBs_Drive]
-- , [FreeGBs_Drive_InMonths_WithExpected_DB_Growth]
-- , [BackupDate_First]
-- , [BackupDate_Last]
-- , [DaysPeriod]
-- , [MinutesForBackup_First]
-- , [MinutesForBackup_Last]
-- , [MinutesForBackup_Delta]
-- , [GB_backup_size_First]
-- , [GB_backup_size_Last]
-- , [GB_BackupGrowth]
-- , [GB_ExpectedDailyGrowth]
-- , [GB_ExpectedAnnualGrowth]
-- , [Total Size in GB]
-- , [Available Space In GB]
-- FROM
-- #Results R
-- WHERE
-- R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth >= 0 AND R.FreeGBs_Drive_InMonths_WithExpected_DB_Growth <= 1
-- END
--IF OBJECT_ID('tempdb..#dbs') > 0
-- DROP TABLE #dbs
--
--IF OBJECT_ID('tempdb..#Drives') > 0
-- DROP TABLE #Drives
--
--IF OBJECT_ID('tempdb..#Results') > 0
-- DROP TABLE #Results
December 16, 2011 at 6:29 am
Does this run on both boxes 2000 and 2005. I executed this script on 2000 box but it threw errors.
//
Server: Msg 156, Level 15, State 1, Line 71
Incorrect syntax near the keyword 'WITH'.
Server: Msg 195, Level 15, State 1, Line 80
'ROW_NUMBER' is not a recognized function name.
//
December 16, 2011 at 6:31 am
Doesn't work on 2000.
December 16, 2011 at 6:44 am
Can you provide any scripts for SQL Server 2000 as well.
December 16, 2011 at 6:49 am
Check what my script does. It can be converted to 2000 with a little work.
December 16, 2011 at 6:52 am
Thanks for your help 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply