July 13, 2009 at 4:30 pm
I have been delegated as the DBA at my work site and I am looking for a way to get notified when a Database autogrows. I would like it to send an email when database growth occours any suggestions on how to do this easily.
July 13, 2009 at 6:19 pm
I know this can be detected from a trace but I don't think you can do it with DDL trigger.. Most DBA's I've worked don't monitor this, they monitor disk space and % changes in disk space, such as less than 10% free or a change by more than 5% in a day..
Stuff like that..
CEWII
July 14, 2009 at 7:54 am
Elliot is right normally you watch database size and available space and manually grow your files with autogrow as a backup. Since you are on 2005 you can query the Default Trace for this information and send out an email. The query would be something like this:
SELECT
TE.[name],
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
I.*
FROM
sys.traces T CROSS Apply
:: fn_trace_gettable(CASE WHEN CHARINDEX('_', T.[path]) 0
THEN SUBSTRING(T.PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'
ELSE T.[path]
End, T.max_files) I JOIN
sys.trace_events AS TE ON I.EventClass = TE.trace_event_id
WHERE
T.is_default = 1 AND -- limit to default trace
TE.NAME Like '%auto grow%' -- willl get Log and Data
Remember that the Default Trace consists of up to 5 20MB files that roll over so the time range available is dependent on the load on the server
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 14, 2009 at 11:02 am
Thanks this is exactly what I needed. The so called SQL expert at my site was supposed to be doing this but failed to do so and cried to my boss that the server is running out of space. went through and shrunk the log file on 1 database from 300 GB down to 1 MB.yes thats GIG. So Know im supposed to be in charge of watching the size of these databases. This should help me keep an Eye on the data base in question and see why it is growing so massive. Thank again.
July 14, 2009 at 11:09 am
JUst remember there is a reason it grew. You need to determine why. Are there regular log backups being done on this database? If not, you need to determine if there should be or the database should be set to the SIMPLE recovery model.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply