autogrow history

Hi,
Is there a way to see when autogrow has been triggered for a particular database/tran log? Over the weekend, we ran out of disk space on a server that has multiple databases, and I'd like to find out which database's tran log was at fault.
Thanks,
SusanThis kind of error should be logged on SQL Server Logs.

Originally posted by dbadba
Hi,

Is there a way to see when autogrow has been triggered for a particular database/tran log? Over the weekend, we ran out of disk space on a server that has multiple databases, and I'd like to find out which database's tran log was at fault.

Thanks,
Susan|||Thanks for the reply!

There is an "out of space" error in the log, but I'm looking for a log (or alert, etc.) that would track each time autogrow was triggered, even when it didn't result in an error. Do you know where I can find that information?

Thanks,
Laura|||Laura,

As far as I know, there is no way to create an alert based on autogrow event, unless you fix a log size limit and then add an alert using "error 9002, severity 19 - The log file for database xxxx is full". I think there is even a template in SQL 2000 Alerts for that.
Another approach would be to define a trace based on transaction log autogrow event. You can use SQL Profiler for tracing; or it can generates the SQL script for you, and then you can include it in a stored procedure that can be scheduled to start at a specific time.

Hope it helps !

Originally posted by dbadba
Thanks for the reply!

There is an "out of space" error in the log, but I'm looking for a log (or alert, etc.) that would track each time autogrow was triggered, even when it didn't result in an error. Do you know where I can find that information?

Thanks,
Laura|||Thanks! I will check into both of the options you suggested.|||if you want to prevent this kind of error from happening you can also create an alert that is triggered when a log is more than a certain percentage full.|||Thank you very much for the suggestion! I appreciate it.|||UNtested but this could be run regularly to email you when the log is over 50% full

create procedure mylog @.dbname varchar(50)
as
declare @.logspace int
, @.wo_num varchar(30) , @.deleted_user varchar(255),@.emails varchar(255) , @.messages varchar(100),
@.subjecttext varchar(100)
truncate table tempdb..logspace
insert tempdb..logspace (dbname,logsize,percentused,status)
execute('dbcc perflog')
set @.logspace = (select percentused from tempdb..logspace where dbname = @.dbname)
print @.logspace
if @.logspace > 50
set @.messages = 'Log Space is over 50%'
set @.subjecttext = @.dbname + 'log is over 50% full'
set @.emails = 'joesmoe@.kokomo.com'
exec master..xp_sendmail @.recipients = @.emails , @.message = @.messages,@.subject = @.subjecttext
else
return

HTH