November 12, 2009 at 4:38 am
Hi all,
we have lot of inserts and updates in our database
how can i know each day how much database growing(i.e transaction per day)
i need to monitor the Daily database growth as per the part of Daily dba task
Please send some useful t-sql script to monitor the Db growth of database
both in sql2000 and 2005
Thanks in advance
November 12, 2009 at 5:05 am
Many scripts available in SSC. please search for it.
you may try this
http://qa.sqlservercentral.com/scripts/Maintenance+and+Management/31929/
November 12, 2009 at 9:34 pm
Thank you very much for the link
I i checked the above link it was working fine in sql server 2005
when i execute the same script in sql 2000 error is
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.objects'.
Can you please update the script for sql2000 databse growth
Thanks in advance
November 12, 2009 at 11:08 pm
Hi,
Find script for sql2000;
If exists (Select name from sysobjects 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.dbid, fileid, size
into #TempDBSize
from sysdatabases sd
join sysaltfiles mf
on sd.dbid = mf.dbID
Order by mf.dbid, sd.name
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.dbid, 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.dbid not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.dbid)
Group by tds.dbid, 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.dbid, fileid, size
into #TempDBSize2
from sysdatabases sd
join sysaltfiles mf
on sd.dbid = mf.dbid
Order by mf.dbid, 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.dbid, 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.dbid = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)
Group by tds.dbid, 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.dbid, 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.dbid not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.dbid)
Group by tds.dbid, tds.DBName)
End
--Select *
--from DBGrowthRate
----Verifies values were entered
--Drop table #TempDBSize2
Rd,
Deepali
November 13, 2009 at 2:21 am
Thank you very much
Its working fine .
manually daily we need to run to Know the Growth of data.
or can i schedule job to run it as its daily .
so that daily or weekly to prepare report
Thanks
November 13, 2009 at 3:02 am
As DBA ; Automation is always preferred..
I will Suggest schedule it during Non peak hours & have ready report at
the start of Day.
Rd,
Deepali
November 13, 2009 at 7:40 am
I look at the system backup history tables to see what growth has been over time. It doesn't require any extra jobs or tables. I just run a script against the existing data that SQL already has stored.
All of our databases, except 1 have a FULL backup run everyday, so I have data every day. My vldb is backed up weekly, which is frequent enough for trending.
November 15, 2009 at 10:26 pm
Thank you very much for u r valuble suggessions dude's
Regards
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply