September 23, 2007 at 11:38 am
Comments posted to this topic are about the item Monitor Database Growth
October 19, 2007 at 4:08 am
The script looks very handy. But i have a small doubt. I am getting the orig size and current size as the same values and as such growth rate is coming up as 0.
Can you guide me on this.
November 29, 2007 at 3:55 am
I think that script have a little mistake. Field size in table sys.master_files contains current filesize in 8-KB pages. 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
January 14, 2008 at 2:39 pm
Can someone tell me why this script always gives the growth amount as 0 even when the origsize and cursize are significantly different.
February 4, 2008 at 10:30 am
For SQL 2000 in part two, you need to make the following changes:
1. Change all references database_id to dbid
2. Change all references to file_id to fileid
3. Change all references to sys.master_files to master.dbo.sysaltfiles
4. Change all references to sys.databases to master.dbo.sysdatabases
February 4, 2008 at 2:30 pm
That is how I have it.
March 13, 2008 at 1:05 pm
hi for some reason i always get the growth size to be 0, do you have an updated script?
March 13, 2008 at 1:09 pm
Has anyone been using this script and has been working for them?
March 28, 2008 at 6:30 am
why the discussion is offline, since we are asking about any updates on this script!
plz tell us someone ( do you find any alternative solution or not ?!)
thnx!
:w00t:
April 10, 2008 at 7:59 pm
Has anyone managed to get this script to run correctly?
"Ask me your questions, bridgekeeper. I'm not afraid"
May 22, 2008 at 3:05 am
I think the second part of part 2 is in error; I think it should be like this:
--Else
IF Exists (Select t.DBName, g.DBName from #TempDBSize2 t
left outer join DBGrowthRate g on T.dbname = g.DBName where g.dbname is null)
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.DBName)
Group by tds.database_ID, tds.DBName)
End
This will do the trick for a newly added database.
June 30, 2008 at 12:12 pm
I modified the script to do what I want and it is working well for me. This is for SQL 2000; you can get it to work with SQL 2005 with a minor tweak of the sys tables.
--Part1
CREATE TABLE [DBGrowthRate] (
[DBGrowthID] [int] IDENTITY (1, 1) NOT NULL ,
[DBName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DBID] [int] NULL ,
[OrigSize] [decimal](10, 2) NULL ,
[CurSize] [decimal](10, 2) NULL ,
[GrowthAmt] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InitialDate] [datetime] NULL ,
[ReportDate] [datetime] NULL CONSTRAINT [DF_ReportDate] DEFAULT (getdate())
) ON [PRIMARY]
GO
--PART 2
--Below is the code run weekly to check the growth.
create Proc usp_DBGrowthRate
AS
Select sd.name as DBName, af.dbid, Sum(af.Size) as NumPages, Convert(decimal(10,2),(Sum(Convert(decimal(10,2),af.Size)) * 8)/1024) as CurSize
into #TempDBSize
from master..sysdatabases sd
join master..sysaltfiles af
on sd.dbid = af.dbid
Group by af.dbid, sd.name
Order by sd.name, af.dbid
If Exists (Select Distinct DBName from #TempDBSize where DBName in (Select Distinct DBName from DBGrowthRate))
and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(InitialDate),101) as InitialDate from DBGrowthRate)
Begin
update dgr
set dgr.CurSize = tds.Cursize
from DBGrowthRate dgr inner join #TempDBSize tds on tds.DBID = dgr.DBID
update dgr
set dgr.GrowthAmt = tds.CurSize - dgr.OrigSize
from DBGrowthRate dgr inner join #TempDBSize tds on tds.DBID = dgr.DBID
update DBGrowthRate
set ReportDate = GetDate()
End
Else
IF Exists (Select Distinct DBName from #TempDBSize where DBName not in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID,OrigSize, CurSize, GrowthAmt, InitialDate)
(Select tds.DBName, tds.dbid,
tds.CurSize,
tds.CurSize,
'0.00 MB', GetDate()
from #TempDBSize tds
where tds.dbid not in (Select Distinct DBID from DBGrowthRate dgr where dgr.DBID = tds.DBID)
)
End
DROP TABLE #TempDBSize
GO
July 29, 2008 at 2:43 pm
for SQL 2000 by changing sys.Master_Files to sysaltfiles and sys.databases to sysdatabases. Make sure to change your column names appropriately if you make this alteration!
What do you mean by Make sure to change your column names appropriately if you make this alteration!
Sorry new at this
July 30, 2008 at 9:13 am
The original script worked for me. I had to, of course, change the size of my database; so I delete a significant amount of history, did a full backup and ran the script again.
Everything worked fine from what I could tell.
"One shalt thou not count, neither count thou two, only then proceeding to three."
November 11, 2008 at 8:17 pm
Worked great for me. Especially w/ the recommended changes for 2000/2005
Thanks a bunch....
JP
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply