Post reply

Transaction Log Growth

  • December 17, 2003 at 7:52 am

    #83985

    I'm looking for some advice (or a script) on how to have SQL 2000 sp3 notify me when the transaction log on my database grows. I had one jump from 15 GB to 45 GB, basically overnight, and if I'm alerted for an increase, perhaps I can determine what's running and creating the huge log. Any advice is always appreciated.

    Terry


    Terry

  • December 17, 2003 at 8:03 am

    #486676

    Setup an SQL Server Performance Condition Alert on counter Percent Log Used in SQL Server Agent.

    Or create script to get log file size and schedule it to run regularly.

  • December 17, 2003 at 8:28 am

    #486688

    Should I use size from sysfiles? Right now it shows 83977 for a 500 MB log.

    Terry


    Terry

  • December 17, 2003 at 9:15 am

    #486715

    Use sp_helpfile or statement from the sp_helpfile below.

    select name, fileid, filename,

    filegroup = filegroup_name(groupid),

    'size' = convert(nvarchar(15), size * 8) + N' KB',

    'maxsize' = (case maxsize when -1 then N'Unlimited'

    else

    convert(nvarchar(15), maxsize * 8) + N' KB' end),

    'growth' = (case status & 0x100000 when 0x100000 then

    convert(nvarchar(3), growth) + N'%'

    else

    convert(nvarchar(15), growth * 8) + N' KB' end),

    'usage' = (case status & 0x40 when 0x40 then 'log only' else 'data only' end)

    from sysfiles

    order by fileid

  • December 17, 2003 at 9:19 am

    #486717

    Also, if I setup the alert, will it write to the event log? We use a product called ELM that sweeps the event log and notifies the appropriate personnel.

    Terry


    Terry

  • December 17, 2003 at 9:28 am

    #486723

    You can configure Alert to send out net message/pager/email to appropriate personnel.

  • December 17, 2003 at 9:52 am

    #486728

    Thanks Allen. I appreciated the suggestions.

    Terry


    Terry

  • December 19, 2003 at 12:52 pm

    #487286

    Hi friends,

    I too face the same prob with transaction Log . Can u please tell me how to Setup an SQL Server Performance Condition Alert on counter Percent Log Used in SQL Server Agent.

    thanks

    Venu


    Venu

Viewing 8 posts - 1 through 7 (of 7 total)

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