Temp DB Full and not able to Shrink? Restart SQL Server is a good option..

I know there are many different blogs about tempdb, but I just wanted to share what I have experienced in my environment.  TempDB was growing very fast and it already grew ~ 120GB but the used space is 100MB, general idea would be to restart the SQL Server services that way new TempDB is re-created.

So I tried first shrinking the files.

DBCC SHRINKFILE ('tempdev', 1024) DBCC SHRINKFILE ('tempdev2', 1024) Still not able to shrink Command(s) completed. It is of no use hmmm interesting.  tempdb_ID  SELECT database_id,*,session_id FROM sys.dm_exec_requests it will give you session ID and by default I believe database_id for tempDB is 2. Now we will see if there are any locks or open transactions by running (DBCC OPENTRAN) on tempBD SELECT * FROM sys.dm_tran_locks where resource_database_id= 2 and to check open transactions you can also write query SELECT * FROM sys.dm_exec_requests WHERE database_id = 2 I verified no locks and no open transactions.  Now I have seen all the tables in tempDB SELECT * FROM tempdb..sysobjects  This may be due to many reasons, may be a developer or user has run a query or  Stored Proc that caused tempDB grow and ran out of space, by creating cached objects on tempDB. When a table is created in Cache they are TRUNCATED instead DELETE because these tables can be reused when the stored procedure is run again. So now what do you do?  Clean Procedure Cache or Restart SQL Server Services? And of-course stored procedures have to be recompiled if you clean up Procedure cache, that is going to be a different topic.  But in my case I simply Cleared Procedure cache in SQL Server 2005 by executing  DBCC FREEPROCCACHE WITH NO_INFOMSGS (this is used to prevent information messages from begin displayed) there by allowing my tempdb to shrink it to bare minimum.  In sql server 2008  Gives you plan_handle and cached entries that are reused SELECT              plan_handle,               objtype,               usecounts,              cacheobjtype,               objtype,               text FROM              sys.dm_exec_cached_plans CROSS APPLY               sys.dm_exec_sql_text(plan_handle) WHERE              usecounts > 1 ORDER BY               usecounts DESC; Gives you memory breakdown of all cached compiled plans SELECT         plan_handle,         ecp.memory_object_address AS CompiledPlan_MemoryObject,         omo.memory_object_address,         pages_allocated_count,         type,         page_size_in_bytes FROM         sys.dm_exec_cached_plans AS ecp JOIN    sys.dm_os_memory_objects AS omo         ON         ecp.memory_object_address = omo.memory_object_address     OR    ecp.memory_object_address = omo.parent_address WHERE         cacheobjtype = 'Compiled Plan'; So I took the plan_handle and cleared only that particular cache there by allowing me shrink TempDB You can clear any specific plan from cache there by allowing tempdb to shrink and gain back space and avoid ing SQL Server services restart. DBCC FREEPROCCACHE (0x0600040057AFE42740A14D80000000000000000000000000);