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?
asked Mar 26, 2015 at 03:57 PM in Default
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!
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.
answered Mar 26, 2015 at 04:33 PM