October 30, 2017 at 2:44 pm
Hi all,
I have a table that has the below columns:
Capture_Date
DB_Name
Schema_Name
Table_Name
Table_Size_MB
The Capture_Date is a datetime column. I have a job that runs nightly and captures this information, however, I would like to run it manually to see which table grew fastest in the past x number of hours. I want a report that shows Capture_Date, Database_Name, Schema_Name, Table_Name and how many MB it grew from one run to the next. I have been trying different things, but have not been able to make it work. I've attached a screenshot of what I'm looking for in the report, the columns are sorted by DB_Name, Schema_Name, Table_Name, Capture_Date DESC.
Thank you so much for your help.
October 31, 2017 at 8:06 am
shahgols - Monday, October 30, 2017 2:44 PMHi all,I have a table that has the below columns:
Capture_Date
DB_Name
Schema_Name
Table_Name
Table_Size_MBThe Capture_Date is a datetime column. I have a job that runs nightly and captures this information, however, I would like to run it manually to see which table grew fastest in the past x number of hours. I want a report that shows Capture_Date, Database_Name, Schema_Name, Table_Name and how many MB it grew from one run to the next. I have been trying different things, but have not been able to make it work. I've attached a screenshot of what I'm looking for in the report, the columns are sorted by DB_Name, Schema_Name, Table_Name, Capture_Date DESC.
Thank you so much for your help.
I bet you would have had more replies by now if you had included DDL and consumable data for people to work with. Looks like you've been up here often but you can refer to the follow for tips on posting:
Forum Etiquette: How to post data/code on a forum to get the best help
Not sure what you have tried and what hasn't worked. I would guess you are looking for something along the lines of the following:
SELECT
Capture_Date,
[DB_Name],
[Schema_Name],
Table_Name,
Table_size_MB - LAG( Table_Size_MB, 1) OVER(PARTITION BY [DB_Name], [Schema_name],Table_Name
ORDER BY Capture_Date) as Growth
FROM YourTable
ORDER BY [DB_Name], [Schema_Name], Table_Name, Capture_Date DESC
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply