November 1, 2016 at 8:13 am
new issue (related to http://www.sqlservercentral.com/Forums/Topic1830020-2799-2.aspx)
i'm trying to set up alerts for tempdb data and tempdb log growth. but i'm getting alerts for log growth but the log is not growing. the tempdb is configured at 50GB data and 8GB log. neither one has grown.
in the alert email, i get a value of 36.00:
DESCRIPTION: The SQL Server performance counter 'Log Growths' (instance 'tempdb') of object 'Databases' is now above the threshold of 0.00 (the current value is 36.00).
this corresponds to the perfmon counter Log Growths, Object <instancename>, Instance tempdb. (See attached pic)
in the alert history, it shows a value of 649 and growing. (see attached pic) i can clear the history and reset this value to zero but i continue to receive alerts for a 36.00 value. the perfmon counter never resets.
what do i need to do to get accurate tempdb log growth alerts? and how to reset the alert correctly?
thanks!
November 1, 2016 at 8:35 am
I don't suppose you can reset the counter - it wouldn't be accurate any more if you did. What you could do is, when the alert fires, run a job that writes the number to a table and then compare that to the number last time it fired. If that's above the acceptable threshold, send an e-mail or do whatever other action is necessary. If you want to be notified every time there's a log growth event, consider using extended events instead.
John
November 1, 2016 at 9:10 am
Instead of an alert on "growing" of the LOG file, you can create an alert on the usage of the log file: 'SQLServer:Databases|Percent Log Used|tempdb|>|99'. Such alert will fire when the log is (almost) full, so just before the log will grow...
November 1, 2016 at 9:36 am
HanShi (11/1/2016)
Instead of an alert on "growing" of the LOG file, you can create an alert on the usage of the log file: 'SQLServer:Databases|Percent Log Used|tempdb|>|99'. Such alert will fire when the log is (almost) full, so just before the log will grow...
that works with the least extra work. 🙂 thanks.
now why isn't there a % usage counter for the datafile side? otherwise, it seems my only option is to monitor the size of the data file, and since it's different across all my servers, not as easy to implement.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply