April 25, 2011 at 2:10 pm
I run this script to get the quarterly growth. Can you please help me modifying this to get the monthly growth?
SELECT
[SQLName]as ServerName
,[DBName]as DatabaseName
,FileStatus
,[FileType]=case when [FileType]='ROWS' then 'DATA' else 'LOG'end
,SUM(a.[Size(MB)])as [CurrentSize(MB)]
,[UsedSpace(MB)]
,[AvailableSpace(MB)]as [AvailableFreeSpace(MB)]
,CONVERT(INTEGER, ((a.[UsedSpace(MB)]*1.0)/(a.[Size(MB)]*1.0))*100.0) AS [Percent Used]
,[ExecuteTime]
,ISNULL((select B.[UsedSpace(MB)]
from [TESTDB].[dbo].DB_USAGEB
where B.[SQLName] = a.[SQLName]
and B.[DBName] = a.[DBName]
and DATEPART(yyyy, B.[ExecuteTime]) = DATEPART(yyyy, DATEADD(mm, -3, a.[ExecuteTime]))
and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -3, a.[ExecuteTime]))),0)/3.0 AS Data_Usage_Growth
from [TESTDB].[dbo].DB_USAGEa
where DATEPART(mm, [ExecuteTime]) in (1,4,7,10)and [Size(MB)] is not null
group by a.[ExecuteTime]
,a.[SQLName]
,a.[DBName]
,FileStatus
,a.[FileType],a.[Size(MB)],a.[UsedSpace(MB)]
,a.[AvailableSpace(MB)]
order by a.[DBName], a.[ExecuteTime]
April 25, 2011 at 2:31 pm
I would think about removing this
and DATEPART(yyyy, B.[ExecuteTime]) = DATEPART(yyyy, DATEADD(mm, -3, a.[ExecuteTime]))
and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -3, a.[ExecuteTime]))),0)/3.0 AS Data_Usage_Growth
And look to integrate something like this in the select list inside of a CTE, and then do your selects for the quarterly growth and monthly growth.
;
with datepract as (
Select '2/1/2011' as DateField
Union all
Select '7/1/2011'
Union all
Select '5/1/2011'
Union all
Select '11/1/2011'
Union All
Select '3/3/2011'
Union All
Select '3/3/2012'
)
Select Count(DateField) as DatesInQuarter,DATEPART(qq,DateField) as DateQuarter
,DATEPART(mm,DateField) as DateMonth
,DATEPART(yy,DateField) as DateYear
From datepract
Group by DATEPART(yy,DateField),DATEPART(mm,DateField),DATEPART(qq,DateField)
Order By DateQuarter
This is not the full solution but merely a mockup of the idea. The key point is to extract which quarter, month, and year each record belongs to and then group based on year, month and quarter in your final select.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 8:33 am
Can some one please help me. I need to have this script ready by end of this month.
April 26, 2011 at 8:54 am
hydbadrose (4/26/2011)
Can some one please help me. I need to have this script ready by end of this month.
Dude, COPY, PASTE, RUN my script. That should get you 99.99% of the way there. The only change you need is to show monthly rather than daily growth... NOT HARD, real simple.
Also please don't cross post, we're already helping you out on your issue on another thread.
April 26, 2011 at 9:11 am
Ninja's_RGR'us (4/26/2011)
hydbadrose (4/26/2011)
Can some one please help me. I need to have this script ready by end of this month.Dude, COPY, PASTE, RUN my script. That should get you 99.99% of the way there. The only change you need is to show monthly rather than daily growth... NOT HARD, real simple.
Also please don't cross post, we're already helping you out on your issue on another thread.
I have to agree with Ninja here. Hey gave you an excellent script that is ready to run.
Without knowing your end to end process - it is the best solution you have.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 9:39 am
Ninja,
I am sorry I did not see your query? Which one is your's?
April 26, 2011 at 9:42 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply