Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    5

    Unanswered: 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,
    Susan

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: autogrow history

    This 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
    Steve

  3. #3
    Join Date
    Jul 2003
    Posts
    5

    Re: autogrow history

    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

  4. #4
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: autogrow history

    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
    Steve

  5. #5
    Join Date
    Jul 2003
    Posts
    5

    Re: autogrow history

    Thanks! I will check into both of the options you suggested.

  6. #6
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    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.
    Johan

  7. #7
    Join Date
    Jul 2003
    Posts
    5
    Thank you very much for the suggestion! I appreciate it.

  8. #8
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    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
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •