x

The tempdb size keeps growing

I was forced to move the tempdb to new folder yesterday. Now I want to investigate why such a big size and growth.Where to start?

more ▼

asked Mar 26, 2015 at 03:57 PM in Default

avatar image

vivekchandra09
731 5 10 16

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I'd start looking at the default trace. If you don't have any process archiving the default trace for you, you might get lucky and won't be overwritten (depending on how busy your instance is.) Please see the query below, copied from The default trace in SQL Server - the power of performance and security auditing. Again, if your developers keep application names, the query will give you the application name, the start and end time the event took place and the user under which the growth occurred. Good starting point!

 SELECT  TE.name AS [EventName] ,
         T.DatabaseName ,
         t.DatabaseID ,
         t.NTDomainName ,
         t.ApplicationName ,
         t.LoginName ,
         t.SPID ,
         t.Duration ,
         t.StartTime ,
         t.EndTime
 FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                               f.[value]
                                                       FROM    sys.fn_trace_getinfo(NULL) f
                                                       WHERE   f.property = 2
                                                     )), DEFAULT) T
         JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
 WHERE   (te.name = 'Data File Auto Grow'
         OR te.name = 'Data File Auto Shrink')
     AND t.DatabaseID = 2
 ORDER BY t.StartTime;

Say, there is no application name and user, at least you'll have an idea between what time this event took place. Start looking into your plan cache (sys.dm_exec_query_stats) and any batch or maintenance jobs that run within that period of time. Start narrowing down what ran on the instance at time.

more ▼

answered Mar 26, 2015 at 04:33 PM

avatar image

DenisT
3.5k 3 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x78
x17
x8
x3

asked: Mar 26, 2015 at 03:57 PM

Seen: 55 times

Last Updated: Mar 26, 2015 at 04:33 PM

Copyright 2017 Redgate Software. Privacy Policy