November 29, 2019 at 8:00 pm
I was going to post this as a HOW-TO question, but I think I've figured it out and now I just want to know Could it be done better?
Here is a sample of the data I had to work with and what I did.
IF OBJECT_ID('tempdb..#SQL_DbGrowthTracking') IS NOT NULL
DROP TABLE #SQL_DbGrowthTracking
CREATE TABLE #SQL_DbGrowthTracking(
DatabaseName VARCHAR(100) NOT NULL,
FileNamePath VARCHAR(512) NOT NULL,
TotalSize_in_MB BIGINT NOT NULL,
CrtDate DATE NOT NULL
)
GO
-------INSERT A COLLECTION OF SAMPLE DATA
INSERT INTO #SQL_DbGrowthTracking --DatabaseName FileNamePath TotalSize_in_MB CrtDate
VALUES ('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-11-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-10-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-09-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-08-29'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-06-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-05-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-04-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\master.mdf', 4 ,'2019-03-27'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-11-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-10-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-09-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-08-29'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-06-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 3 ,'2019-05-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 2 ,'2019-04-01'),
('master', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\mastlog.ldf', 2 ,'2019-03-27'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-11-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-10-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-09-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-08-29'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-06-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-05-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-04-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\model.mdf', 8 ,'2019-03-27'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-11-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-10-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-09-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-08-29'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-06-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-05-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-04-01'),
('model', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\modellog.ldf', 8 ,'2019-03-27'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 162 ,'2019-11-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 134 ,'2019-10-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 122 ,'2019-09-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 122 ,'2019-08-29'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 56 ,'2019-06-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 38 ,'2019-05-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 17 ,'2019-04-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBData.mdf', 17 ,'2019-03-27'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-11-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-10-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-09-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-08-29'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-06-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-05-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-04-01'),
('msdb', 'E:\SysDb2016\MSSQL13.SQL2016\MSSQL\DATA\MSDBLog.ldf', 28 ,'2019-03-27'),
('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-11-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-10-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-09-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-08-29'),
('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-06-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-05-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-04-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb.mdf', 512 ,'2019-03-27'),
('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-11-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-10-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-09-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-08-29'),
('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-06-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-05-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-04-01'),
('tempdb', 'F:\SQLtempDB2016\tempdb2.ndf', 512 ,'2019-03-27'),
('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-11-01'),
('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-10-01'),
('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-09-01'),
('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-08-29'),
('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-06-01'),
('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-05-01'),
('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-04-01'),
('tempdb', 'G:\SQLTEMPLOGS2016\tempdb.ldf', 128 ,'2019-03-27')
------------ 20191129 QUERY THAT SAMPLE DATA FOR RESULTS THAT SHOW DB GROWTH DATES, AND AMOUNTS
;WITH a AS (
SELECT
-- RowNmr = ROW_NUMBER() OVER (PARTITION BY FileNamePath ORDER BY CrtDateTime),
DatabaseName,
FileNamePath,
PreviousSize = LAG(TotalSize_in_MB) OVER (PARTITION BY FileNamePath ORDER BY CrtDate),
TotalSize_in_MB,
CrtDate
FROM #SQL_DbGrowthTracking
WHERE CrtDate > GETDATE()-280
)
SELECT
FirstDate = (SELECT MIN(CONVERT(DATE, CrtDate)) FROM #SQL_DbGrowthTracking WITH (NOLOCK)),
-- a.RowNmr,
a.DatabaseName,
a.FileNamePath,
-- a.PreviousSize,
a.TotalSize_in_MB,
FileGrowthInMB = CASE WHEN a.TotalSize_in_MB > a.PreviousSize THEN a.TotalSize_in_MB - a.PreviousSize
ELSE a.PreviousSize - a.TotalSize_in_MB
END,
a.CrtDate
FROM a
WHERE TotalSize_in_MB <> PreviousSize
ORDER BY
FileNamePath,
CrtDate DESC
--
November 30, 2019 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 1, 2019 at 6:46 am
WHy are you doing this part?
FileGrowthInMB = CASE WHEN a.TotalSize_in_MB > a.PreviousSize THEN a.TotalSize_in_MB - a.PreviousSizeELSE a.PreviousSize - a.TotalSize_in_MB
You're reporting the size change as growth whether or not it's actually growth. Say for the sake of argument, you use SHRINKDB (I'm not saying it's a good idea, but sometimes it's necessary), the file size of your shrunk database should decrease, not increase. I don't see the need for the CASE statement at all. Why not just do the simple subtraction, TotalSize_in_MB - PreviousSize?
December 2, 2019 at 1:39 pm
This is a DEV server and sometimes DBs are recreated from TFS.
You are correct though. My initial thought was to capture the change in size as a positive value, but it make much more sense to capture a decrease as a negative value, other wise I would need to add another column or add more code to change the result to varchar and identify whether it was growth or reduced. That is not needed...
Thanks.
December 2, 2019 at 7:53 pm
A few quick thoughts (numbered to make discussion of them easier):
(1) You need to normalize this design, you're storing huge amounts of duplicate data. Specifically, encode the database name with your own permanent id (not the system database_id, because that can change). Also, encode the the path name and file name; yes, I'd at least split out the actual file name from the path. Note that you can use a single table to store all the strings, since the id is meaningless except as a code for the string. You can use a view to pre-code the lookups so every query doesn't have to specify them.
(2) For best overall performance, you'll want to cluster the table first on Cr[ea]t[e]Date; in this case, I suggest adding an identity column to make the clustering key unique.
(3) I also suggest using datetime and not just date for when the size is captured. This prevents misleading results. It also allows multiple captures per day, in case you want to do that later, or in case at some point you have a file that is growing unexpectedly for some reason, and you want to narrow the time window when it grows.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply