Monitor Database Growth

  • All SQL Folks...

    The original script has MAJOR error....

    --In PART2 --- where it runs to update the growth table...it tries to convert the DATE to CHAR...and then compares with GetDATE() -- which is not correct.

    --- I have corrected the script...BELOW>..replace the script code with Following for your EXISTS condition....This works good for SQL 2000....Change your EXISTS condition accordingly for 2005 script too..

    -PART 2 -- CORRECTED EXISTS Section below...Replace on original script.;-)

    ...

    ...

    If Exists (Select Distinct DBName from #TempDBSize2

    where DBName in (Select Distinct DBName from DBGrowthRate))

    and GetDate() > (Select Distinct Max(MetricDate) from DBGrowthRate)

  • How do you get it to insert rather than update? I want to keep track of the changes in size weekly...

    ¤ §unshine ¤

  • All...

    I have corrected the code..As it has flaws dealing with date comparison...And hence you are not getting the GROWTH amount on weekly runs...

    Here is the UPDATED code (works for 2005, 2008)....Also, I have converted the GrowthAMT to Numeric (instead of VARCHAR), --- Allowing you to export the results in EXCEL for further number crunching

    Let me know if this works for your environments.

    --PART 1 (UPDATED, Jignesh Rajgor 08/25/2009)

    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 decimal(10,2),

    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 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 GetDate() > (Select Distinct Max(MetricDate) 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(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))) - dgr.CurSize )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, dgr.OrigSize)

    End

    --Select *

    --from DBGrowthRate

    ----Verifies values were entered

    Drop table #TempDBSize2

  • ...Here is the 2000 Version..of the code....

    --PART 1

    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 decimal(10,2),

    MetricDate datetime)

    Select sd.name as DBName, mf.name as FileName, mf.dbid, fileid, size

    into #TempDBSize

    from master.dbo.sysdatabases sd

    join master.dbo.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 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 master.dbo.sysdatabases sd

    join master.dbo.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 GetDate() > (Select Distinct Max(MetricDate) 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(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))) - dgr.CurSize )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, dgr.OrigSize)

    End

    --Select *

    --from DBGrowthRate

    ----Verifies values were entered

    Drop table #TempDBSize2

  • I will let you know... thanks...

  • Found this thread and thought I'd post my version for anyone still following it

    HTH

    Dave J

    /****** Object: StoredProcedure [dbo].[usp_Check_DB_growth] Script Date: 06/14/2011 10:14:18 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[usp_Check_DB_growth] @initialise INT = NULL

    AS

    /*

    This can be setup on a weekly/monthly schedule or run on an ad-hoc basis

    On the first run use

    exec usp_Check_DB_growth @initialise = 1

    and it will create a table called DBGrowthRate in the current DB

    New databases will be picked up if they are added over time

    Note: The first results will always show 0 Growth as you need to run it at least twice,

    with one database having grown in between, to see any meaningful results.

    Based on a script found at http://www.sqlservercentral.com/Forums/Topic401684-513-1.aspx

    Dave Jackson

    http://glossopian.co.uk

    */

    SET NOCOUNT ON

    IF @initialise = 1

    BEGIN

    IF EXISTS (SELECT name FROM sys.objects WHERE name = 'DBGrowthRate' AND TYPE = 'U')

    BEGIN

    Raiserror('The table DBGrowthRate already exists in this database. Please drop it manually to re-create.', 18,1)

    Return

    END

    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 DECIMAL(10, 2),

    metricdate DATETIME

    )

    END

    IF NOT EXISTS (SELECT name FROM sys.objects WHERE name = 'DBGrowthRate' AND TYPE = 'U')

    BEGIN

    Raiserror('The table DBGrowthRate does not exists in this database. Please run this procedure with the @initialise parameter = 1.', 18,1)

    Return

    END

    -- Check for any new Databases and add them if there are any

    If Exists(Select 1 FROM sys.databases sd

    WHERE sd.database_id NOT IN (SELECT DISTINCT [DBID] FROM DBGrowthRate))

    BEGIN

    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)) * 8)/ 1024.0) AS origsize,

    CONVERT(DECIMAL(10, 2), (SUM(CONVERT(DECIMAL(10, 2), tds.size)) * 8)/ 1024.0) AS cursize,

    0 AS growthamt,

    Getdate() AS metricdate

    FROM #tempdbsize tds

    WHERE tds.database_id NOT IN (SELECT DISTINCT [DBID]

    FROM DBGrowthRate)

    GROUP BY tds.database_id,

    tds.dbname

    DROP TABLE #tempdbsize

    END

    --Below is the code to run periodically to check the growth.

    BEGIN

    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 Getdate() > (SELECT DISTINCT MAX(metricdate) 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)) * 8)/ 1024.0) AS cursize,

    CONVERT(DECIMAL(10, 2), (SUM(CONVERT(DECIMAL(10, 2), tds.size)) * 8)/ 1024.0) - dgr.cursize 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,

    dgr.origsize

    HAVING (CONVERT(DECIMAL(10, 2), (SUM(CONVERT(DECIMAL(10, 2), tds.size)) * 8)/ 1024.0) - dgr.cursize <> 0)

    END

    DROP TABLE #tempdbsize2

    SELECT t1.dbname,

    t1.[DBID],

    MAX(t1.numpages) [Num Pages],

    MAX(t1.origsize) [Orig Size MB],

    MAX(t1.cursize) [Cur Size MB],

    t1.growthamt [Growth Amt MB],

    isNull(MAX(t3.metricdate), MAX(t2.metricdate)) [Last Metric Date],

    MAX(t2.metricdate) [Metric Date],

    DATEDIFF(d,isNull(MAX(t3.metricdate), MAX(t2.metricdate)), MAX(t2.metricdate)) [Days between Growth]

    FROM dbo.DBGrowthRate t1

    INNER JOIN (SELECT MAX(metricdate) metricdate,

    [DBID]

    FROM dbo.DBGrowthRate

    GROUP BY [DBID]) t2

    ON t1.[DBID] = t2.[DBID]

    AND t1.metricdate = t2.metricdate

    LEFT JOIN (SELECT metricdate,

    [DBID]

    FROM dbo.DBGrowthRate) t3

    ON t1.[DBID] = t3.[DBID]

    AND t3.metricdate < t2.metricdate

    GROUP BY t1.dbname,

    t1.[DBID],

    t1.growthamt

    ORDER BY MAX(t2.metricdate) desc

    END

    GO


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • even it was not working for me too. as it is showing 0 mb growth rate.

  • raj-211912 (6/20/2011)


    even it was not working for me too. as it is showing 0 mb growth rate.

    I've amended my version, please see the updated comments section.

    HTH

    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • Hi Dave J,

    Thanks a lot for the script...

    The script provides the current Db size. but I would like to know is there any way that we can store the the DB size details for our future reference. That will help us for capacity planning

    Regards,

    Gokl

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply