April 27, 2011 at 8:47 am
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...
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
-------------------------------------------------------------------------------
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:10 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
April 27, 2011 at 9:16 am
Thanks for your help and I will let you know if I get the correct result.
April 27, 2011 at 9:35 am
Please don't start multiple threads for the same problem. You're just wasting people's time is they answer unaware of all that's been discussed.
No replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1097279-391-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply