Tracking DB grows

Question

Post reply

Tracking DB grows

Viewing 3 posts - 16 through 17 (of 17 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Ideas are also in the link I've mentioned included.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Tmp SSCommitted Points: 1938 More actions · Answer 2

Frank, thanks for your help. I take a look on the link (you was dealing with the same issue, right?), and realize that the most simple solution, i mean check the file modification date, works for me because no restarts occurs since the last DB grow and the autoshrink option is disable...

Thanks again.

Tmp

Bill Nye Hall of Fame Points: 3248 More actions · Answer 3

Perhaps this will help.

To track growth of data file:

Make alert called 'dbsize':

type: performance condition alert

counter: Data File(s) size

Alert if counter: Rises above ?(in kb)

Make job called 'increase dbsize alert':

Include the following TSQL (largely copied from generate script

task of alert, with lines added to set new file size for alert):



DECLARE @SZ int, @perfcond nvarchar(150)



select @sz=(size*8)+300 from sysfiles where fileid=1

--size is in # of 8k pages.  Add 300 each time the alert fires this job.

--File size for alert is in KB of actual data file (mdf).



set @perfcond =N'SQLServer:Databases|Data File(s) Size (KB)|Northwind|>|' 

set @perfcond =@perfcond +cast(@sz as nvarchar)



IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'dbsize'))

 ---- Delete the alert with the same name.

EXECUTE msdb.dbo.sp_delete_alert @name = N'dbsize' 

BEGIN 

EXECUTE msdb.dbo.sp_add_alert @name = N'dbsize', @message_id = 0, @severity = 

0, @enabled = 1, @delay_between_responses = 60, @notification_message = N'File grew again', @performance_condition =  @perfcond, @include_event_description_in = 4, @job_name = N'increase dbsize alert', @category_name = N'[Uncategorized]'



EXECUTE msdb.dbo.sp_add_notification @alert_name = N'dbsize', @operator_name 

= N'Bill', @notification_method =4



END

This re-creates the alert with a larger counter hurdle each time the file

grows (otherwise, it will just keep alerting that you're over the original alert amount).

Edited by - billnye101 on 12/02/2003 12:13:02 PM