Post reply

Alert when TempDB is growing rapidly

  • November 18, 2013 at 11:16 am

    #300275

    I had a issue where tempdb grew rapidly in a short period of time. Tempdb file growth is restricted so it did not grow to a point where I ran out of disk space, but it got to a point where tempdb could no longer grow. The reason for this is being worked out.

    Is there a way I can setup a alert where I would receive an email if tempdb files grow so much in a period of time? For example, if tempdb grew by 10 GB within 10 minutes.

  • November 18, 2013 at 2:52 pm

    #1667249

    You could setup a job to determine the TEMPDB size and runs every ten minutes. Store the values in a table and determine the increase between the runs. If the increase between two jobs is beyond your threshold send a mail using sp_send_dbmail.

    You could also monitor the number of autogrowth events in a period of time. These autogrowth events are monitored by the default trace, so you can query the default trace directly to get the number of events.

    -- declare variable

    declare @value sql_variant

    -- determine current tracefile of default trace (SQL 2005 and up)

    SELECT

    @value = value

    FROM

    fn_trace_getinfo(default)

    WHERE

    traceid = 1

    and property = 2;

    -- read the tracefile for event 92 (Data File Auto Grow) and 93 (Log File Auto Grow) and groep result

    SELECT

    DatabaseName

    , FileName

    , min(EndTime) as FirstAutogrowth

    , max(EndTime) as LastAutogrowth

    , count(EndTime) as Autogrowth_Actions

    FROM

    fn_trace_gettable(cast(@value as NVARCHAR(200)),1)

    WHERE

    EventClass IN (92, 93)

    GROUP BY

    DatabaseName

    , FileName

    ORDER BY

    max(EndTime) desc

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Viewing 2 posts - 1 through 1 (of 1 total)

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