April 26, 2011 at 2:53 pm
hydbadrose (4/26/2011)
I gave up- I am not getting any friendly reply...
People here are truly trying to help you. We need more information though.
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 3:58 pm
hydbadrose (4/26/2011)
I gave up- I am not getting any friendly reply...
I gave you a fully tested, fully working solution which as far as I know you have not even bothered to try. What is it exactly that you call friendly answer?
You want me to pay you to try my own stuff???????
April 26, 2011 at 7:32 pm
I tried all the options and nothing is working.Here is the store procedure that I am using to capture the information. I need a script to generate the monthly growth report. I would be grateful, if could help me providing the script to generate the monthly growth. Thanks for the patience and your generosity.
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*******************************************************************************************
Purpose : To keep Database & files details for Capacity Planning and growth trends.
*******************************************************************************************/
CREATE PROC [dbo].[dba_CapacityPlanning]
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM MSDB.sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[tbl_CapacityPlanning]') AND type in (N'U'))
BEGIN
CREATE TABLE [msdb].[dbo].[tbl_CapacityPlanning](
[ExecuteTime] [datetime] NULL,
[SQLBuild] [nvarchar](57) NULL,
[SQLName] [nvarchar](128) NULL,
[DBName] [sysname] NULL,
[LogicalFileName] [sysname] NULL,
[DBCreationDate] [datetime] NULL,
[DBRecoveryModel] [nvarchar](60) NULL,
[DBCompatibilityLevel] [tinyint] NULL,
[DBCollation] [sysname] NULL,
[FileType] [nvarchar](60) NULL,
[FileName] [nvarchar](260) NULL,
[Growth] [float] NULL,
[GrowthType] [varchar](30) NULL,
[FileID] [int] NULL,
[IsPrimaryFile] [bit] NULL,
[MaxSize(MB)] [float] NULL,
[Size(MB)] [float] NULL,
[UsedSpace(MB)] [float] NULL,
[AvailableSpace(MB)] [float] NULL,
[FileStatus] [nvarchar](60) NULL,
[IsOffline] [bit] NULL,
[IsReadOnly] [bit] NOT NULL,
[IsReadOnlyMedia] [bit] NULL,
[IsSparse] [bit] NULL
) ON [PRIMARY]
END
CREATE table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
UsedExtents int, Name sysname, FileName nchar(520))
DECLARE @DatabaseName varchar(500)
DECLARE curDB cursor for
SELECT ltrim(rtrim(name)) from master.sys.databases where state_desc='ONLINE'
AND user_access_desc='MULTI_USER'
open curDB
fetch curDB into @DatabaseName
while @@fetch_status = 0
begin
insert into #tmpspc exec ('USE [' + @DatabaseName + '] DBCC SHOWFILESTATS')
fetch curDB into @DatabaseName
end
close curDB
deallocate curDB
create table #tmplogspc (DatabaseName sysname, LogSize float, SpaceUsedPerc float, Status bit)
insert #tmplogspc EXEC ('dbcc sqlperf(logspace)')
insert into [msdb].[dbo].[tbl_CapacityPlanning] SELECT getdate() AS [ExecuteTime],
left(@@version,57) AS [SQLBuild], @@servername AS [SQLName],
sd.name AS [DBName],
s.name AS [LogicalFileName],
sd.create_date AS [DBCreationDate], sd.recovery_model_desc AS [DBRecoveryModel],
sd.compatibility_level AS [DBCompatibilityLevel], sd.collation_name AS [DBCollation],
s.type_desc AS [FileType],
s.physical_name AS [FileName],
CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE (s.growth*8)/1024 END AS float) AS [Growth],
CAST(CASE WHEN s.is_percent_growth=1 THEN '%' Else 'MB' END AS VARCHAR) AS [GrowthType],
s.file_id AS [FileID],
CAST(CASE s.file_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsPrimaryFile],
CASE when s.max_size=-1 then -1 else (s.max_size * CONVERT(float,8))/1024 END AS [MaxSize(MB)],
(s.size * CONVERT(float,8))/1024 AS [Size(MB)],
(CAST(tspc.UsedExtents*convert(float,64) AS float))/1024 AS [UsedSpace(MB)],
((tspc.TotalExtents - tspc.UsedExtents)*convert(float,64))/1024 AS [AvailableSpace(MB)],
s.state_desc AS [FileStatus],
CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],
s.is_read_only AS [IsReadOnly],
s.is_media_read_only AS [IsReadOnlyMedia],
s.is_sparse AS [IsSparse]
FROM master.sys.master_files AS s
INNER JOIN master.sys.databases sd ON sd.database_id=s.database_id
INNER JOIN #tmpspc tspc ON ltrim(rtrim(tspc.FileName)) = ltrim(rtrim(s.physical_name))
UNION ALL
SELECT getdate() AS [ExecuteTime],left(@@version,57) AS [SQLBuild], @@servername AS [SQLName],
sd.name AS [DBName],
s.name AS [LogicalName],
sd.create_date AS [DBCreationDate], sd.recovery_model_desc AS [DBRecoveryModel],
sd.compatibility_level AS [DBCompatibilityLevel], sd.collation_name AS [DBCollation],
s.type_desc AS [FileType],
s.physical_name AS [FileName],
CAST(CASE s.is_percent_growth WHEN 1 THEN s.growth ELSE (s.growth*8)/1024 END AS float) AS [Growth],
CAST(CASE WHEN s.is_percent_growth=1 THEN '%' Else 'MB' END AS VARCHAR) AS [GrowthType],
s.file_id AS [FileID],
'0' as [IsPrimaryFile],
CASE when s.max_size=-1 then -1 else (s.max_size * CONVERT(float,8))/1024 END AS [MaxSize(MB)],
(s.size * CONVERT(float,8))/1024 AS [Size(MB)],
(tspclog.LogSize * tspclog.SpaceUsedPerc * 10.24)/1024 AS [UsedSpace(MB)],
((s.size * CONVERT(float,8))/1024 - (tspclog.LogSize * tspclog.SpaceUsedPerc * 10.24)/1024)
AS [AvailableSpace(MB)],
s.state_desc AS [FileStatus],
CAST(case s.state when 6 then 1 else 0 end AS bit) AS [IsOffline],
s.is_read_only AS [IsReadOnly],
s.is_media_read_only AS [IsReadOnlyMedia],
s.is_sparse AS [IsSparse]
FROM master.sys.master_files AS s
INNER JOIN master.sys.databases sd ON sd.database_id=s.database_id
INNER JOIN #tmplogspc tspclog ON
tspclog.DatabaseName = sd.name
WHERE (s.type = 1 ) ORDER BY sd.name, FileID ASC
-- DROP THE TEMP TABLES
DROP TABLE #tmpspc
DROP TABLE #tmplogspc
END
GO
April 27, 2011 at 9:51 am
Hi hydbadros,
I'm not sure how you would like to calculate the growth from your table, but here is an example of how it can be done, where I have tried to guess what things you want to group by and which data to calculate growth on:
SELECT YEAR(ExecuteTime) AS [Year], MONTH(ExecuteTime) AS [Month],
LogicalFileName,
MAX([UsedSpace(MB)]) - MIN([UsedSpace(MB)]) AS [MB Growth],
(MAX([UsedSpace(MB)]) - MIN([UsedSpace(MB)])) / MIN([UsedSpace(MB)]) AS [Percent Growth]
FROM tbl_CapacityPlanning
GROUP BY YEAR(ExecuteTime), MONTH(ExecuteTime), LogicalFileName
Hope this helps you in the right direction.
/Markus
Thank you so much- It worked. I really appreciate your help on this. God Bless you Markus!
April 27, 2011 at 10:05 am
hydbadrose (4/27/2011)
Hi hydbadros,I'm not sure how you would like to calculate the growth from your table, but here is an example of how it can be done, where I have tried to guess what things you want to group by and which data to calculate growth on:
SELECT YEAR(ExecuteTime) AS [Year], MONTH(ExecuteTime) AS [Month],
LogicalFileName,
MAX([UsedSpace(MB)]) - MIN([UsedSpace(MB)]) AS [MB Growth],
(MAX([UsedSpace(MB)]) - MIN([UsedSpace(MB)])) / MIN([UsedSpace(MB)]) AS [Percent Growth]
FROM tbl_CapacityPlanning
GROUP BY YEAR(ExecuteTime), MONTH(ExecuteTime), LogicalFileName
Hope this helps you in the right direction.
/Markus
Thank you so much- It worked. I really appreciate your help on this. God Bless you Markus!
Wow it's only the exact same thing I posted, only less accurate and without warnings and hd space and autogrowth.
I'm glad "you" got it figured out.
April 27, 2011 at 9:14 pm
Thanks Ninja for your help as well and sorry to bother you.
September 14, 2012 at 4:36 am
Here what is DBINFO table
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply