May 18, 2009 at 1:11 pm
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.
May 18, 2009 at 1:20 pm
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.May 18, 2009 at 1:34 pm
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:(
May 18, 2009 at 1:51 pm
Go to this link. It has all you need.
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31929/
May 18, 2009 at 1:52 pm
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
May 18, 2009 at 1:56 pm
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
May 19, 2009 at 7:21 am
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