Script to Monitor Database Growth for Sqlserver 2000 & 2005

Posted: February 25, 2008 in Sqlserver


__________________________________________________________________________________________________________________________________________________

Monitor Database Growth For SQLSERVER2005
__________________________________________________________________________________________________________________________________________________

–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_In_MB decimal(10,2), CurSize_In_MB decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)

Drop table #TempDBSize

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_In_MB, CurSize_In_MB, 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

_____________________________________________________________________________________________________________


Monitor Database Growth FOR SQLSERVER2000

_____________________________________________________________________________________________________________

–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_In_MB decimal(10,2), CurSize_In_MB decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)

Drop table #TempDBSize

Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize
from sysdatabase sd
join sysaltfiles mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize_In_MB, CurSize_In_MB, 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

Thanks
Md. S.Hassan
Sqlserver Database Administrator

Advertisements
Comments
  1. Peter says:

    I have used the 2000 version of this script. Fabulous … except there are a few errors. Corrected script is below:

    –PART 1

    If exists (Select name from sysobjects where name = ‘DBGrowthRate’ and Type = ‘U’)
    Drop Table dbo.DBGrowthRate

    — Create table to store size data
    Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
    NumPages int, OrigSize_In_MB decimal(10,2), CurSize_In_MB decimal(10,2), GrowthAmt varchar(100),
    MetricDate datetime)

    — Enter baseline sizes
    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_In_MB, CurSize_In_MB, 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

    — Check initial data
    Select *
    from DBGrowthRate

    –PART 2
    — Run periodically to check DB 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_In_MB, CurSize_In_MB, GrowthAmt, MetricDate)
    (Select tds.DBName, tds.dbid, Sum(tds.[Size]) as NumPages, dgr.CurSize_In_MB 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_In_MB)) + ‘ 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_In_MB)
    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_In_MB, CurSize_In_MB, 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

    — View
    Select *
    from DBGrowthRate

    Drop table #TempDBSize2

  2. Charles says:

    Its good, How to run this accross multiple instances?. Kindly provide me the script

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s