Log file size alert question

  • I get alert everyday around 1 am in the morning that Prod DB log file >80%. My log file is around 75GB and 99% free When I check during business hours.

    I know there is an index maintenance job runs during that time and it generates 25-30GB log and after that rest of the day only few MBs to few GBs (when I check log backup file around 1-2 am).

    My question is since log generated by Index job is only 15-20GB log how come it gets alerts that log file is > 80 ?

    Any input is appreciated.

  • Can you post the sql (code) from the alert?

  • SQLServer Alert System: 'ProdDatabase Percent LogUsed 80%' occurred on \\ProdserverAZ

  • Is it possible that the log is indeed filled to 80% used at some point during the index maintenance? A transaction log backup would most likely decrease the size of the log and thereby reduce the % used if one is occurring during or after that job is running. If not, I would say that there may be an issue with the monitor (alert) itself...

  • Another thing you may want to do is for the response on the alert, you could execute a job that just queries sys.databases to see what the log_reuse_wait value is.

    Or you can just run a job that does the same, appends the results to a text file, start running prior to when you usually get the alert, run every 5 or 10 mins and run for however long and just check the text file the next day to see what was going on. Easier to do with the Alert though I would think.

    There could just be a lot of other things such as replication, an active transaction, mirroring, etc. Often times you can figure it out by checking log_reuse_wait and what it is would give you some ideas on how to manage it.

    Sue

  • Thanks everyone for the inputs

Viewing 6 posts - 1 through 5 (of 5 total)

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