April 1, 2008 at 8:39 am
hi,
i have this database growth query to see how much database is growing .but it cant work with sql 2000 plz check it i have tried to make change for sql 2000 but it cant work so plz help me ???
query is
--PART 1
If exists (Select name from sys.objects where name = 'DBGrowthRate' and Type = 'U')
Drop Table dbo.DBGrowthRate
Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
NumPages int, OrigSize decimal(10,2), CurSize decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
'0.00 MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)
Drop table #TempDBSize
Select *
from DBGrowthRate
--Above creates initial table and checks initial data
--PART 2
--Below is the code run weekly to check the growth.
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize2
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name
If Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(MetricDate),101) as MetricDate
from DBGrowthRate)
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
Convert(varchar(100),(Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))
- dgr.CurSize)) + ' MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
join DBGrowthRate dgr
on tds.database_ID = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)
Group by tds.database_ID, tds.DBName, dgr.CurSize)
End
Else
IF Not Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
'0.00 MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)
End
--Select *
--from DBGrowthRate
----Verifies values were entered
Drop table #TempDBSize2
April 1, 2008 at 7:38 pm
Fortunately, I just did this a few months ago. The PART 2 part that runs every day or so is in this procedure. Note the revisions to the table layout and bug fix suggested by itmastera in case you already have the permanent table setup.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/*-----------------------------------------------------------------------------------
Project : Monitor Database Growth
Description:
Created By : Brandie Tarvin
Created : 2007/05/16
Revisions : ksullivan 01/22/08:
b) Renamed column GrowthAmt to GrowthAmtMB and changed its data type
from varchar(100) to decimal(10,2) to make it easier to query.
c) Added the size correction posted by itmastera 11/29/2007 10:55 AM to
the SqlServerCentral article.
d) Put the code into a procedure.
ksullivan 01/25/08:
a) Reworked for sql2000 sysdatabases etc instead of sys.databases etc
Notes : Originally appeared in
http://qa.sqlservercentral.com/scripts/Maintenance+and+Management/31929/
with title "Monitor Database Growth"
by Brandie Tarvin, 2007/05/16
itmastera 11/29/2007:
So, this part of script is not right:
Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024.
Author wants to get file size in MB, but it must be like this:
Sum(Convert(decimal(10,2),tds.Size)) * 8192)/1024)/1024.
But the easiest way to get file size in MB is to write this:
Sum(Convert(decimal(10,2),tds.Size)) * 8/1024
Confirmed at http://www.mssqltips.com/tip.asp?tip=1349 :
FileSize - Multiply the size column which is the number of 8K pages by 8 and then divide by 1024 to convert the number to megabytes.
--- Sample Call ---
exec dbo.[up_UtilDBGrowthRateSql2000]
--------------------------------------------------------------------------------------*/
alter PROC [dbo].[up_UtilDBGrowthRateSql2000]
AS
set nocount ON
declare @KeyDate datetime
set @KeyDate = DateAdd(day, DateDiff(day, 0, getdate()), 0)
if object_id( 'tempdb.dbo.#TempDBSize2' ) is not null drop table #TempDBSize2
Select sd.name as DBName, mf.name as FileName, mf.dbid, mf.fileid, mf.size, @KeyDate as KeyDate
into #TempDBSize2
from master.dbo.sysdatabases sd
join master.dbo.sysaltfiles mf
on sd.dbid = mf.dbid
Order by mf.dbid, sd.name
if object_id( 'tempdb.dbo.#MaxMetricDate' ) is not null drop table #MaxMetricDate
select DBName, max(DateAdd(day, DateDiff(day, 0, MetricDate), 0)) MaxMetricDate
into #MaxMetricDate
from dbo.DBGrowthRate
group by DBName
-- Add the latest values for existing dbs
if exists
(
select * from #TempDBSize2 tds
where exists
(
select * from #MaxMetricDate mmd where tds.DBName = mmd.DBName and tds.KeyDate > mmd.MaxMetricDate
) -- b
) -- a
begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmtMB, MetricDate)
(
Select
tds.DBName,
tds.dbid,
Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2), Sum(Convert(decimal(10,2), tds.Size)) * 8/1024) as CurSize,
(Convert(decimal(10,2), Sum(Convert(decimal(10,2), tds.Size)) * 8/1024)- dgr.CurSize) as GrowthAmtMB,
GetDate() as MetricDate
from #TempDBSize2 tds
join dbo.DBGrowthRate dgr
on tds.dbid = dgr.DBID
Where DBGrowthID = (Select Max(DBGrowthID) from dbo.DBGrowthRate where DBID = dgr.DBID)
Group by tds.dbid, tds.DBName, dgr.CurSize
)
end
-- Add the latest values for new dbs
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmtMB, MetricDate)
(
Select
tds.DBName,
tds.dbid,
Sum(tds.Size) as NumPages,
Convert(decimal(10,2), Sum(Convert(decimal(10,2),tds.Size)) * 8/1024) as OrigSize,
Convert(decimal(10,2), Sum(Convert(decimal(10,2),tds.Size)) * 8/1024) as CurSize,
0.00 as GrowthAmtMB,
GetDate() as MetricDate
from #TempDBSize2 tds
where not exists
(
select * from #MaxMetricDate mmd where tds.DBName = mmd.DBName -- and tds.KeyDate > mmd.MaxMetricDate
) -- b
Group by tds.dbid, tds.DBName
)
End
----------------------------------------------------------------------------
April 2, 2008 at 4:16 am
Really thaxx for reply still there is error comes out
Server: Msg 208, Level 16, State 6, Procedure up_UtilDBGrowthRateSql2000, Line 90
Invalid object name 'dbo.up_UtilDBGrowthRateSql2000'.
what can i change now
Thanxx in Advance
April 2, 2008 at 7:27 am
you have to
CREATE PROC [dbo].[up_UtilDBGrowthRateSql2000]
before you can
ALTER PROC [dbo].[up_UtilDBGrowthRateSql2000]
Which means, run all of the code ONCE with the create statement. After that, if you make any change run all of the code with the alter statement.
When you put the code into a scheduled job all you need is this line in a job step:
exec [NameOfYourDb].[dbo].[up_UtilDBGrowthRateSql2000]
April 2, 2008 at 7:52 am
hi,
Really very sorry but still i cant do .will u plz tell me how can i solve this .
sorry for inconvenience.will u plz just edit this portion on query plz if u dont mind when i try it comes error . i dont know where i m doing mistakes
thaxxx
April 2, 2008 at 9:41 am
-- Parts 1 and 2 are to be run in Query Analyzer
-- PART 1 Make a table to hold the results
if exists (select 1 from information_schema.tables where table_name = 'DBGrowthRate')
drop table DBGrowthRate
CREATE TABLE [dbo].[DBGrowthRate]
(
[DBGrowthID] [int] IDENTITY(1,1) NOT NULL,
[DBName] [varchar](100) NULL,
[DBID] [int] NULL,
[NumPages] [int] NULL,
[OrigSize] [decimal](10, 2) NULL,
[CurSize] [decimal](10, 2) NULL,
[GrowthAmtMB] [decimal](10, 2) NULL,
[MetricDate] [datetime] NULL
) ON [PRIMARY]
-- You do not need the rest of the code that was in the original part 1 because
-- the procedure will insert the first rows.
GO
--------------------------------------------------------------------------------
-- PART 2 Make the procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/*------------------------------------------------------------------------------
File :
Project : Monitor Database Growth
Description:
Created By : Brandie Tarvin
Created : 2007/05/16
Revisions : ksullivan 01/22/08:
b) Renamed column GrowthAmt to GrowthAmtMB and changed its data type
from varchar(100) to decimal(10,2) to make it easier to query.
c) Added the size correction posted by itmastera 11/29/2007 10:55 AM to
the SqlServerCentral article.
d) Put the code into a procedure.
ksullivan 01/25/08:
a) Reworked for sql2000 sysdatabases etc instead of sys.databases etc
Notes : Originally appeared in
http://qa.sqlservercentral.com/scripts/Maintenance+and+Management/31929/
with title "Monitor Database Growth"
by Brandie Tarvin, 2007/05/16
itmastera 11/29/2007:
So, this part of script is not right:
Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024.
Author wants to get file size in MB, but it must be like this:
Sum(Convert(decimal(10,2),tds.Size)) * 8192)/1024)/1024.
But the easiest way to get file size in MB is to write this:
Sum(Convert(decimal(10,2),tds.Size)) * 8/1024
Confirmed at http://www.mssqltips.com/tip.asp?tip=1349 :
FileSize - Multiply the size column which is the number of 8K pages by 8 and then divide by 1024 to convert the number to megabytes.
--- Sample Call ---
exec dbo.[up_UtilDBGrowthRateSql2000]
------------------------------------------------------------------------------*/
create PROC [dbo].[up_UtilDBGrowthRateSql2000]
AS
set nocount ON
declare @KeyDate datetime
set @KeyDate = DateAdd(day, DateDiff(day, 0, getdate()), 0)
if object_id( 'tempdb.dbo.#TempDBSize2' ) is not null drop table #TempDBSize2
Select sd.name as DBName, mf.name as FileName, mf.dbid, mf.fileid, mf.size, @KeyDate as KeyDate
into #TempDBSize2
from master.dbo.sysdatabases sd
join master.dbo.sysaltfiles mf
on sd.dbid = mf.dbid
Order by mf.dbid, sd.name
if object_id( 'tempdb.dbo.#MaxMetricDate' ) is not null drop table #MaxMetricDate
select DBName, max(DateAdd(day, DateDiff(day, 0, MetricDate), 0)) MaxMetricDate
into #MaxMetricDate
from dbo.DBGrowthRate
group by DBName
-- Add the latest values for existing dbs
if exists
(
select * from #TempDBSize2 tds
where exists
(
select * from #MaxMetricDate mmd where tds.DBName = mmd.DBName and tds.KeyDate > mmd.MaxMetricDate
) -- b
) -- a
begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmtMB, MetricDate)
(
Select
tds.DBName,
tds.dbid,
Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2), Sum(Convert(decimal(10,2), tds.Size)) * 8/1024) as CurSize,
(Convert(decimal(10,2), Sum(Convert(decimal(10,2), tds.Size)) * 8/1024)- dgr.CurSize) as GrowthAmtMB,
GetDate() as MetricDate
from #TempDBSize2 tds
join dbo.DBGrowthRate dgr
on tds.dbid = dgr.DBID
Where DBGrowthID = (Select Max(DBGrowthID) from dbo.DBGrowthRate where DBID = dgr.DBID)
Group by tds.dbid, tds.DBName, dgr.CurSize
)
end
-- Add the latest values for new dbs
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmtMB, MetricDate)
(
Select
tds.DBName,
tds.dbid,
Sum(tds.Size) as NumPages,
Convert(decimal(10,2), Sum(Convert(decimal(10,2),tds.Size)) * 8/1024) as OrigSize,
Convert(decimal(10,2), Sum(Convert(decimal(10,2),tds.Size)) * 8/1024) as CurSize,
0.00 as GrowthAmtMB,
GetDate() as MetricDate
from #TempDBSize2 tds
where not exists
(
select * from #MaxMetricDate mmd where tds.DBName = mmd.DBName -- and tds.KeyDate > mmd.MaxMetricDate
) -- b
Group by tds.dbid, tds.DBName
)
End
GO
--------------------------------------------------------------------------------
-- PART 3 Make a job that runs the procedure. Put this in the job step
exec [NameOfYourDb].dbo.[up_UtilDBGrowthRateSql2000]
April 3, 2008 at 7:31 am
Hi,
Thanks for the modified code. Does anyone know why its still showing 0 as growth in mb
even when the size has actually changed?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply