April 25, 2011 at 11:23 am
I'm using Chad Miller's Powershell-based solution here for capacity planning.
April 25, 2011 at 11:53 am
At a guess, something like this
WITH cte ([ServerName],[Database Name],[Month],[DB-Size])
AS (
SELECT [ServerName],[Database Name],DATEDIFF(month,GETDATE(),[Execution date]) AS [Month],[DB-Size]
FROM
WHERE [Execution date] >= DATEADD(month,DATEDIFF(month,0,GETDATE())-12,0)
AND [Execution date] = DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
AND DAY([Execution date]) = 1)
SELECT [ServerName],[Database Name],
MAX(CASE WHEN [Month]= 0 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [0],
MAX(CASE WHEN [Month]= -1 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-1],
MAX(CASE WHEN [Month]= -2 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-2],
MAX(CASE WHEN [Month]= -3 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-3],
MAX(CASE WHEN [Month]= -4 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-4],
MAX(CASE WHEN [Month]= -5 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-5],
MAX(CASE WHEN [Month]= -6 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-6],
MAX(CASE WHEN [Month]= -7 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-7],
MAX(CASE WHEN [Month]= -8 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-8],
MAX(CASE WHEN [Month]= -9 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-9],
MAX(CASE WHEN [Month]=-10 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-10],
MAX(CASE WHEN [Month]=-11 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-11]
FROM cte a
JOIN cte b ON b.[Month]=a.[Month]-1
GROUP BY [ServerName],[Database Name]
ORDER BY [ServerName],[Database Name] ASC
This will show last 12 months DB file growth, substitute DB-Used for data growth.
----------------------
I ran the above script. I am not getting any information. not even with 0 value.
April 25, 2011 at 1:26 pm
I used this script to get the quarterly growth, but I have to modify to get the monthly growth. Can you please help.
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_USAGE B
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_USAGE a
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 4:38 pm
Did you actually run my script? It gives you the date you're going to runout of space on EACH drive on the server + the next autogrowth event.
Also it would be real easy to modify to get the monthly average (I personnally use daily).
April 26, 2011 at 4:37 am
hydbadrose (4/25/2011)
I used this script to get the quarterly growth, but I have to modify to get the monthly growth. Can you please help.
Remove the month selection in the WHERE clause and change -3 to -1 in the DATEADD in the correlated query.
Far away is close at hand in the images of elsewhere.
Anon.
April 26, 2011 at 8:32 am
How about this statement? we were deviding by 3 to get the quarterly data.
and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -3, a.[ExecuteTime]))),0)/3.0 AS Data_Usage_Growth
April 26, 2011 at 8:51 am
hydbadrose (4/26/2011)
How about this statement? we were deviding by 3 to get the quarterly data.and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -3, a.[ExecuteTime]))),0)/3.0 AS Data_Usage_Growth
That would give you an AVERAGE monthly growth not quarterly.
Remove the divide by 3
and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -3, a.[ExecuteTime]))),0) AS Data_Usage_Growth
would give you a quarterly growth
and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -1, a.[ExecuteTime]))),0) AS Data_Usage_Growth
will give you a monthly growth
Personally I would rewrite the query but would require the following
Create table DDL including indexes
Samply data to test with (important especially for ExecuteTime, ie does it contain time and only exists once per month?)
Far away is close at hand in the images of elsewhere.
Anon.
April 26, 2011 at 9:32 am
Ok so for the following script how would you change in whole;
,ISNULL((select B.[UsedSpace(MB)]
from [TESTDB].[dbo].DB_USAGE B
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
April 26, 2011 at 9:36 am
hydbadrose (4/26/2011)
Ok so for the following script how would you change in whole;,ISNULL((select B.[UsedSpace(MB)]
from [TESTDB].[dbo].DB_USAGE B
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
Hello, read english? I gave you a full complete solution. USE IT. If it's not what you need, past back and we'll help.
April 26, 2011 at 9:41 am
hydbadrose (4/26/2011)
Ok so for the following script how would you change in whole;
As I already stated
without this there is not much we can do
Far away is close at hand in the images of elsewhere.
Anon.
April 26, 2011 at 10:17 am
I am sorry for the confusion. I used the following script and I am getting this error with only one database information is displaying instead 6 database including systems db's.
,ISNULL((select B.[UsedSpace(MB)]
from [TESTDB].[dbo].DB_USAGE B
where B.[SQLName] = a.[SQLName]
and B.[DBName] = a.[DBName]
and DATEPART(yyyy, B.[ExecuteTime]) = DATEPART(yyyy, DATEADD(mm, -1, a.[ExecuteTime]))
and DATEPART(MM, B.[ExecuteTime]) = DATEPART(MM, DATEADD(mm, -1, a.[ExecuteTime]))),0) AS Data_Usage_Growth
***subquery returned more than 1 value. This is not permitted when the subquery follows=,!=,<=,>= or when subquery is used as an expression.***
April 26, 2011 at 10:20 am
I asked very early on for your process, and then Dave has asked again in a more clear way for the same info.
We really need to see the script to create the table you are using to hold this data. We need to see the script you are using to put data into that table. And we need sample data to give you a better solution that any that have been offered thus far.
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 10:24 am
Syed Jahanzaib Bin hassan (4/22/2011)
Check this script if you have any problem or modification required then tell me
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
Here's a modification:
DECLARE @StartTime DATETIME, @EndTime DATETIME
SET @StartTime = DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
SET @EndTime = DATEADD(dd,1+DATEDIFF(dd,0,GETDATE()),0)
SELECT
DBNAME,
CURRENT_LOG_SIZE = SUM(CASE WHEN TYPE_DESC = 'LOG' AND SIZE_DATE >= @StartTime AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128,
TOTAL_DATABASE_SIZE = (SUM(CASE WHEN TYPE_DESC = 'LOG' AND SIZE_DATE >= @StartTime AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128)
+ (SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= @StartTime AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128),
CURRENT_DATA_SIZE = SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= @StartTime AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128,
LAST_DAY_DATA_SIZE = SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= DATEADD(dd,-1,@StartTime) AND SIZE_DATE < DATEADD(dd,-1,@EndTime) THEN FILE_SIZE ELSE 0 END) / 128,
PER_DAY_INCREMENT = (SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= @StartTime AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128)
- (SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= DATEADD(dd,-1,@StartTime) AND SIZE_DATE < DATEADD(dd,-1,@EndTime) THEN FILE_SIZE ELSE 0 END) / 128),
PER_MONTH_INCREMENT = (SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= DATEADD(dd,-30,@StartTime) AND SIZE_DATE < @EndTime THEN FILE_SIZE ELSE 0 END) / 128)
- (SUM(CASE WHEN TYPE_DESC = 'ROWS' AND SIZE_DATE >= DATEADD(dd,-60,@StartTime) AND SIZE_DATE < DATEADD(dd,-30,@EndTime) THEN FILE_SIZE ELSE 0 END) / 128)
FROM DBINFO
WHERE TYPE_DESC IN ('LOG','ROWS')
AND SIZE_DATE >= DATEADD(dd,-60,@StartTime)
GROUP BY DBNAME
The original version has an uncomfortable proportion of hobby code in it.
My DBA would have a bigger problem if I saw him using this:
FROM DBINFO TMP1
WHERE DBID IN(SELECT DISTINCT DBID FROM DBINFO)
He'd be returned to HR.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 26, 2011 at 10:27 am
David Burrows (4/26/2011)
hydbadrose (4/26/2011)
Ok so for the following script how would you change in whole;As I already stated
- DDL to create the table (including indexes)
- Sample data to test with (important especially for ExecuteTime, ie does it contain time and only exists once per month?)
without this there is not much we can do
Stop panicking for 1 second. Get an english interpreter if you need to. READ WHAT WE SAY. Stop focussing on your darn problem and let us help you.
April 26, 2011 at 2:49 pm
I gave up- I am not getting any friendly reply...
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply