October 20, 2014 at 8:50 am
Can someone please help me on below query to add a column that calculates the difference in amount of DATAFILE growth on daily basis. I am actually trying to collect the daily database datafile growth information. Thank you.
SELECT
DB.name,
SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) AS DataFileSizeGB,
SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024.0 /1024.0 ELSE 0 END) AS LogFileSizeGB,
GETDATE() AS Datalogged
FROM
sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.source_database_id is null -- exclude snapshots
GROUP BY DB.name
ORDER BY DataFileSizeGB DESC
October 20, 2014 at 8:57 am
Keep collecting the data as you are doing, and use a view to show the daily difference. You can use the ROW_NUMBER function to number each row, then join back to itself on RowNumber = RowNumber - 1.
John
October 20, 2014 at 9:20 am
I think you really want to change your query. Your actual file sizes shouldn't change on a daily basis, but the space used by the files could and should change on a daily basis.
I use a query like this (must be in the context of the database to work, so you have to do some dynamic SQL, unfortunately, to get every database):
SELECT
DB.name,
SUM(CASE WHEN type = 0 THEN MF.size * 8 / 1024.0 / 1024.0
ELSE 0
END) AS DataFileSizeGB,
SUM(CASE WHEN type = 0 THEN FILEPROPERTY(MF.name, 'SpaceUsed') * 8 / 1024.0 / 1024.0
ELSE 0
END) AS DataFileUsedSizeGB,
SUM(CASE WHEN type = 1 THEN MF.size * 8 / 1024.0 / 1024.0
ELSE 0
END) AS LogFileSizeGB,
SUM(CASE WHEN type = 1 THEN FILEPROPERTY(MF.name, 'SpaceUsed') * 8 / 1024.0 / 1024.0
ELSE 0
END) AS LogFileUsedSizeGB,
GETDATE() AS Datalogged
FROM
sys.master_files MF
JOIN sys.databases DB
ON DB.database_id = MF.database_id
WHERE
DB.source_database_id IS NULL -- exclude snapshots
GROUP BY
DB.name,
MF.name
ORDER BY
DataFileSizeGB DESC
Here's a link to a stored procedure (stored in OneDrive) that I have used in the past for this type of monitoring, http://1drv.ms/1wfIbfR. It is based on the code in sp_spaceused system procedure and also utilizes some of Ola Hallengren's maintenance solution, https://ola.hallengren.com/.
If you really just care about what database files have actually grown in the past day vs. usage, you can query the default trace to get that information. Here's a link to a stored procedure I use for that, also on OneDrive, http://1drv.ms/1wfIWFz
Remember, don't use any code you get from the internet without understanding it first.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2014 at 11:36 am
Thanks John and Jack for your information.
But, this is what the O/P i am expecting....TO GET 4th column with difference in growth from datafilesize column...
name DataFileSizeGB LogFileSizeGBDatalogged Growth Increase_MB
AdventureWorks2012 0.2001953125000.0007324218752014-10-20 13:33:40.473 0
AdventureWorksDW2012 0.1965332031250.0007324218752014-10-20 13:33:40.473 0
msdb 0.0179443359370.0255737304682014-10-20 13:33:40.473 0
tempdb 0.0078125000000.0004882812502014-10-20 13:33:40.473 0
ReportServer 0.0049438476560.0067138671872014-10-20 13:33:40.473 0
AdventureWorks2012 0.4001953125000.0007324218752014-10-21 13:33:40.473 20
AdventureWorksDW2012 0.2965332031250.0007324218752014-10-21 13:33:40.473 10
msdb 0.0179443359370.0255737304682014-10-21 13:33:40.473
tempdb 0.0078125000000.0004882812502014-10-21 13:33:40.473
ReportServer 0.0049438476560.0067138671872014-10-21 13:33:40.473
October 20, 2014 at 12:03 pm
DBA_Learner (10/20/2014)
Thanks John and Jack for your information.But, this is what the O/P i am expecting....TO GET 4th column with difference in growth from datafilesize column...
name DataFileSizeGB LogFileSizeGBDatalogged Growth Increase_MB
AdventureWorks2012 0.2001953125000.0007324218752014-10-20 13:33:40.473 0
AdventureWorksDW2012 0.1965332031250.0007324218752014-10-20 13:33:40.473 0
msdb 0.0179443359370.0255737304682014-10-20 13:33:40.473 0
tempdb 0.0078125000000.0004882812502014-10-20 13:33:40.473 0
ReportServer 0.0049438476560.0067138671872014-10-20 13:33:40.473 0
AdventureWorks2012 0.4001953125000.0007324218752014-10-21 13:33:40.473 20
AdventureWorksDW2012 0.2965332031250.0007324218752014-10-21 13:33:40.473 10
msdb 0.0179443359370.0255737304682014-10-21 13:33:40.473
tempdb 0.0078125000000.0004882812502014-10-21 13:33:40.473
ReportServer 0.0049438476560.0067138671872014-10-21 13:33:40.473
John did try to lead you there. I'm trying to give you what I think is a better way to measure what you are trying to measure. In a properly managed SQL Server, database files should never grow automatically, the DBA should be monitoring the space used and free space in database files and manually grow the files during a maintenance window or low usage time before an autogrow needs to happen. The scripts I provided can help you monitor space used and free space AND show you how much database files have grown when autogrows take place.
Here's the code John was trying to lead you to:
WITH dbSizes AS
(
SELECT
*, /* replace this with your desired column list */
/* ordering DESC means the most recent row will be 1 */
ROW_NUMBER() OVER (PARTITION BY name ORDER BY DataLogged DESC) AS rowNo
FROM
databaseSizes
)
SELECT
*, /* replace this with your desired column list */
(A.DataFileSizeGB - B.DataFileSizeGB)/1024.0 AS DataFileSizeChangeInMB,
(A.LogFileSizeGB - B.LogFileSizeGB)/1024.0 AS LogFileSizeChangeInMB
FROM
dbSizes AS A
LEFT JOIN dbSizes AS B
ON A.name = B.name AND
/* A has the most recent row (1) so it needs to be B - 1 to match */
A.rowNo = B.rowNo - 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply