dts and databas growth
-
I am looking to find a little script to check database growth on a daily bases in a MSSQL environment. This will run as a stored proceedure in a dts package Does anyone have a little script like this
-
Create the following table and stored procedure. Schedule the proc to run in a sql job:
CREATE TABLE [DBAFileInfo] (
[HistId] [int] IDENTITY (1, 1) NOT NULL ,
[SrvName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DBName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FileID] [int] NULL ,
[CurrentSize] [int] NULL ,
[LastSize] [int] NULL ,
[LastGrowth] AS ([CurrentSize] - [LastSize]) ,
[MeasureDate] [datetime] NULL CONSTRAINT [DF__DBAFileIn__Measu__08211BE3] DEFAULT (getdate()),
CONSTRAINT [PKHistID] PRIMARY KEY CLUSTERED
(
[HistId]
 
WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]
GO
create procedure sp_MonFileGrowth as
declare @MyIdentity int
declare @OperEmail varchar(32)
set @OperEmail = 'your email address'
-- Insert database information into DBAFileInfo (note: multiply by 8 because size is database extent)
set @MyIdentity = (select max(HistId) from DBAFileInfo) -- save off where new entries start
insert into DBAFileInfo (SrvName, DBName, FileID, CurrentSize)
exec sp_MSforeachdb @command1 =
'select @@ServerName, db_name(dbid), sf.fileid, sf.size * 8
from sysdatabases sd, ?..sysfiles sf
where
db_name(dbid) = ''?'' '
-- Update last size from the latest current size for new entries
update DBAFileInfo
set LastSize =
(select top 1 B.CurrentSize from DBAFileInfo B
where B.DBName = A.DBName and B.FileID = A.FileID and B.HistID < A.HistID order by HistID DESC)
FROM DBAFileInfo A
WHERE A.HistId > @MyIdentity
-- Send mail message with attachment to the Operator with Databases having changed file sizes (today)
if exists
(select * from DBAFileInfo where CurrentSize > LastSize
and MeasureDate > convert(datetime, CONVERT(char(10),GETDATE(),101)))
begin
exec xp_sendmail
@recipients = @OperEmail,
@subject = 'The following Databases had file size growth!',
@query =
'select
substring(@@ServerName,1,24) as Server,
DBName,
CurrentSize as CurrentSizeKB,
LastSize as LastSizeKB
from DBAFileInfo
where CurrentSize > LastSize
and MeasureDate > convert(datetime, CONVERT(char(10),GETDATE(),101))',
@attach_results = 'TRUE', @width = 500
end
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply