Find below steps to fix the tempdb issues.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d83d8511-1036-464f-bf90-6ee970dff580/tempdb-full-unable-to-shrink?forum=sqldatabaseengine

1)DBCC SHRINKFILE ('tempdev', 1024)

The query executed successfully but the size of the database did not change.

2)SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

No open transactions! Alright, any process holding locks on tempdb?

3)select * from sys.dm_tran_locks where resource_database_id= 2

No locks! There was a mention of sys.dm_db_session_space_usage DMV which helps to track the number of page allocation and deallocation by each session on the instance. Hence tried my luck with this query.

4)select * from sys.dm_db_session_space_usage where user_objects_alloc_page_count<> 0

Any session would use tempdb for creating some temporary objects. Since the database was not shrinking, obviously some user defined tables would be there

5)SELECT * FROM sys.tables where is_ms_shipped = 0

Check for user tables on tempdb

After performing all the above steps, Space is very big constraint than execute the below command to freeup the cache.

6)DBCC FREEPROCCACHE

7)DBCC SHRINKFILE ('tempdev', 1024)

USE AdventureWorks2012; GO SELECT * FROM Person.Address; GO SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st WHERE text LIKE N'SELECT * FROM Person.Address%'; GO

SELECT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount,s.plan_handle, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec ,query_plan FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t ORDER BY MaxElapsedTime DESC

-- Remove the specific plan from the cache. DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000); GO

--Clearing all plans from the plan cache DBCC FREEPROCCACHE WITH NO_INFOMSGS;

--Clearing all cache entries associated with a resource pool

SELECT * FROM sys.dm_resource_governor_resource_pools; GO DBCC FREEPROCCACHE ('default'); GO

8)-- Session usage in tempdb /* In addition to looking at how much space is used by each of the object types within tempdb, you can also see how much space each session has used. This can be useful for identifying the job or person that s using most of tempdb or is displaying a pattern of usage that could be causing space problems now or in the future. */

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT es.session_id , ec.connection_id ,ec.plan_handle , es.login_name , es.host_name , st.text , su.user_objects_alloc_page_count , su.user_objects_dealloc_page_count , su.internal_objects_alloc_page_count , su.internal_objects_dealloc_page_count , ec.last_read , ec.last_write , es.program_name FROM sys.dm_db_session_space_usage su INNER JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id LEFT OUTER JOIN sys.dm_exec_connections ec ON su.session_id = ec.most_recent_session_id OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st outer apply sys.exec_cached_plans cp WHERE su.session_id > 50 order by

9) Current SQL server Temp DB statement-allocated and deallocated space on TempDB SELECT ssu.session_id, (ssu.internal_objects_alloc_page_count + sess_alloc) as allocated, (ssu.internal_objects_dealloc_page_count + sess_dealloc) as deallocated , stm.TEXT from sys.dm_db_session_space_usage as ssu, sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stm , (select session_id, sum(internal_objects_alloc_page_count) as sess_alloc, sum (internal_objects_dealloc_page_count) as sess_dealloc from sys.dm_db_task_space_usage group by session_id) as tsk where ssu.session_id = tsk.session_id and ssu.session_id >50 and ssu.session_id = req.session_id and ssu.database_id = 2 order by allocated DESC 9) Solution:

If you are not able to release space from TempDB because entire Space in TempDB will be unallocated space, Try the below T-SQL

USE TempDB GO DBCC FREEPROCCACHE /*Clears the procedure cache*/ GO DBCC DROPCLEANBUFFERS /*Forces all dirty pages for the current database to be written to disk and cleans the buffers*/ GO DBCC FREESYSTEMCACHE ('ALL') /*Releases all unused cache entries from all caches*/ GO DBCC FREESESSIONCACHE /*Flushes the distributed query connection cache*/ GO CHECKPOINT /*Writes all dirty pages for the current database to disk*/ GO Now you will be able to shrink TempDB as all the caches will be free in TempDB

select * from sys.dm_db_file_space_usage

select * from sys.dm_db_session_space_usage

select* from sys.dm_db_task_space_usage

10) statement consuming space on tempdb use TempDB GO

SELECT t1.session_id, t1.request_id, t1.task_alloc, t1.task_dealloc, t2.sql_handle, t2.statement_start_offset, t2.statement_end_offset, t2.plan_handle FROM (Select session_id, request_id, SUM(internal_objects_alloc_page_count) AS task_alloc, SUM (internal_objects_dealloc_page_count) AS task_dealloc FROM sys.dm_db_task_space_usage GROUP BY session_id, request_id) AS t1, sys.dm_exec_requests AS t2 WHERE t1.session_id = t2.session_id AND (t1.request_id = t2.request_id) ORDER BY t1.task_alloc DESC

11)--------long running query in temp db use TempDB GO SELECT transaction_id FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;

----Worker table information select * from sys.dm_tran_active_transactions s1 inner join sys.dm_tran_database_transactions s2 on s1.transaction_id = s2.transaction_id

--I would hope to kill the sessions and have the objects cleaned up. Then I could shrink tempdb. However,

select * from sys.dm_tran_session_transactions

returns no rows. So these "transactions" aren't bound to any sessions, right? So now what do I do? Well, I went to figure out which sessions are owning these worktable objects:

select session_id, database_id, user_objects_alloc_page_count, user_objects_dealloc_page_count, internal_objects_alloc_page_count, internal_objects_dealloc_page_count, waittime,lastwaittype, login_time, last_batch, status, cmd, loginname, sql_handle from sys.dm_db_task_space_usage s1 inner join sys.sysprocesses s2 on s1.session_id = s2.spid

12) Please open a new query window in Sql Server Management Studio. Please paste the entire script in the query window and then please Execute the script. A sample output is indicated below for reference.

-- checks if tempdb files are created of equal size

IF (EXISTS( SELECT name, size, physical_name FROM tempdb.sys.database_files WHERE type_desc = 'ROWS' AND size <> (SELECT MAX(size) FROM tempdb.sys.database_files WHERE type_desc = 'ROWS'))) BEGIN SET @bSuggestions = 1; PRINT N'File sizes of tempdb data files do not appear to be equal. ' + N'Please verify initial size is same for all tempdb data files.'; END