Monitor Database Growth

  • Comments posted to this topic are about the item Monitor Database Growth

  • 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.

  • 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

  • Can someone tell me why this script always gives the growth amount as 0 even when the origsize and cursize are significantly different.

  • 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

  • That is how I have it.

  • hi for some reason i always get the growth size to be 0, do you have an updated script?

  • Has anyone been using this script and has been working for them?

  • 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:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Has anyone managed to get this script to run correctly?

    "Ask me your questions, bridgekeeper. I'm not afraid"

  • 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.

  • 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

  • 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

  • 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."

  • 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