January 5, 2017 at 7:34 am
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.
January 5, 2017 at 10:39 am
Can you post the sql (code) from the alert?
January 5, 2017 at 10:52 am
SQLServer Alert System: 'ProdDatabase Percent LogUsed 80%' occurred on \\ProdserverAZ
January 5, 2017 at 11:30 am
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...
January 5, 2017 at 11:44 am
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
January 5, 2017 at 1:25 pm
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