TRACKING AND RECORDING DATABASE GROWTH

  • Hi guys,

    Is there a way we can track and record(store within db tables) the databse growth?I need to get the info when the databse grows by a ceratin % or by a certain MB.I cannot use the in-built alerts because I have to run this on SQL Express.btw I am backing up databases daily .I do not know if that is some place we can look.I can definitely see an increase in size of the db by looking at backups of the past 7 days but I want to record this info automatically and store it in a table within the databse.Can any of you expers help me with this issue?

    thanks in adavance.

  • We do have a home grown monitoring application to do that -unfortunately (for you) this is propietary code so I can't share it.

    On the other hand I can tell you what it does.

    It keeps track of database growth granular to table level, available space on disks and backup status.

    Application sends a summary daily report -granular to database level via email and a page to oncall when some thresholds are reached.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Thank you for the reply paul.Man if i knew how to accomplish what i described in my post I would not have posted it:-D.Unfortunately I do not have the skills like u guys:(

  • Go to this link. It has all you need.

    http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31929/

  • I have a job that does this:

    set nocount on;

    declare @sql nvarchar(max);

    select @sql =

    coalesce(

    @sql + 'insert into dbo.SpaceUsed(TableName, Rows, Reserved, Data, Index_Size, Unused) exec sp_spaceused ''' + name + ''', ''true'';',

    'insert into dbo.SpaceUsed(TableName, Rows, Reserved, Data, Index_Size, Unused) exec sp_spaceused ''' + name + ''', ''true'';')

    from sys.tables;

    exec (@SQL);

    The logging table is defined as:

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[SpaceUsed](

    [Date] [datetime] NOT NULL DEFAULT (getdate()),

    [TableName] [nvarchar](128) NOT NULL,

    [Rows] [char](11) NULL,

    [Reserved] [varchar](18) NULL,

    [Data] [varchar](18) NULL,

    [Index_Size] [varchar](18) NULL,

    [Unused] [varchar](18) NULL,

    CONSTRAINT [PK_SpaceUsed] PRIMARY KEY CLUSTERED

    (

    [Date] ASC,

    [TableName] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    It works pretty well.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I just wrote this query, its in its crudest form so it does kind off what u want:

    IF EXISTS (SELECT * FROM tempdb..sysobjects

    WHERE id = object_id(N'[tempdb]..[#tempTest]'))

    drop table #tempTest

    create table #tempTest (dbname nvarchar(100),

    db_size nvarchar(100),

    owner nvarchar(100),

    dbid int,

    created datetime,

    statusnvarchar(4000),

    compatibility_level int

    )

    insert into #temptest

    exec sp_helpdb

  • thank you all for all your valuable input.this has helped me setup something close to what i was looking for.:-)

Viewing 7 posts - 1 through 6 (of 6 total)

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