question

vivekchandra09 avatar image
vivekchandra09 asked

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?
tempdbsizedb-sizesizing
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
DenisT avatar image
DenisT answered
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][1]. 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. [1]: https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.