Tempdb shrink in sqlserver

 

Process can take a long time to complete.  Status can be checked by

select session_id, 

blocking_session_id, 

db_name(database_id) as [Database],

command, 

percent_complete, 

wait_type,

wait_time, 

wait_resource, 

scheduler_id, 

Qry.text 

  from sys.dm_exec_requests req 

cross apply sys.fn_get_sql(req.sql_handle) as Qry 

This SQL will show all currently running processes and percentage done

if the tempdb is not shrink after two attempts then follow the below steps.

DBCC FREEPROCCACHE

Run 

   use tempdb

   go

dbcc shrinkfile (tempdev, 'target size in MB')

   go

   -- this command shrinks the primary data file

Use same command above using the file name of the secondary datafiles to shrink all data files

Datafile names can be found U:\MSSQL\TempDB\Data

dbcc shrinkfile (templog, 'target size in MB')

   go

   -- this command shrinks the log file