HelpFull Querys -1

--- QUERY STATUS 

select T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
       , R.cpu_time, R.total_elapsed_time, R.percent_complete
from   sys.dm_exec_requests R
       cross apply sys.dm_exec_sql_text(R.sql_handle) T --where  db_name(R.database_id)='tempdb'
order by Command


---- TEMPDB SHRINK

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
dbcc shrinkfile (tempdev,5000)
go

--Troubleshoot if any opentran or locks or allocation

 

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

select * from sys.dm_tran_locks where resource_database_id= 2

select * from sys.dm_db_session_space_usage where user_objects_alloc_page_count<> 0

 

SELECT * FROM tempdb.sys.all_objects where is_ms_shipped = 0

 --if the above Query has objects it will clears by running freeproccache

 

--Some time cache prevents it to shrink

 

DBCC FREESYSTEMCACHE ('ALL')

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE