-- monitor the space for tempdb 

SELECT
SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb,
SUM (User_object_reserved_page_count +
internal_object_reserved_page_count +
version_store_reserved_page_count +
unallocated_extent_page_count +
mixed_extent_page_count) * 8 as total_space
FROM sys.dm_db_file_space_usage

--shrink tempdb 
USE [tempdb] 
GO 
DBCC FREEPROCCACHE  -- CAUTION: this will free up all cache plans
GO 
USE [tempdb] 
GO 
DBCC SHRINKFILE (N'templog' , 10000) -- will shrink the ldf file to 10GB
GO 

Set nocount on


--This script displays what's going on behind the scenes of TempDB database
--This script is compatible with SQL Server 2005 only.


If Exists (Select Name from SysObjects where Name='#Tmp_TempData')
Begin
	Drop Table #Tmp_TempData
End

If Exists (Select Name from SysObjects where Name='#Tmp_Ops_TempDATA')
Begin
	Drop Table #Tmp_Ops_TempDATA
End

--Dump all SQL Handles from TempDB into #Tmp_Ops_TempDATA
SELECT 
	t3.sql_handle as 'SQLHandle', 
	t3.statement_start_offset as 'Statement_Start_OffSET',
	t3.statement_end_offset as 'Statement_End_OffSET'
Into #Tmp_Ops_TempDATA
from sys.dm_db_session_space_usage as t1, sys.dm_exec_requests t3,
(select session_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) as t2
where t1.session_id = t2.session_id and t1.session_id >50
and t1.database_id = 2   --- tempdb is database_id=2
and t1.session_id = t3.session_id


--Go thru each handle and display the results
Declare @SQLHandle VarBinary(64),@StartOffSet int,@EndOffSet int
Declare CheckOps_TempDATA Cursor For Select [SQLHandle],Statement_Start_OffSET,Statement_End_OffSET from #Tmp_Ops_TempDATA
Open CheckOps_TempDATA
	Fetch Next from CheckOps_TempDATA into @SQLHandle,@StartOffSet,@EndOffSet
	While (@@Fetch_Status=0)
	Begin
	--Insert into #Tmp_TempData 
		Select 
			DB_Name(qt.dbid) as 'Database_Name',
			substring(qt.text,s.statement_start_offset/2, 
			(case when s.statement_end_offset = -1 
			then len(convert(nvarchar(max), qt.text)) * 2 
			else s.statement_end_offset end -s.statement_start_offset)/2) 
			as 'SQL_Statement'
			,s.statement_start_offset as 'Start_OffSET'
			,s.statement_end_offset as 'End_OffSET'
			,qt.text as 'TSQL_Batch'
			,qt.objectid as 'Object_ID'
			,s.execution_count as 'Execution_Count'
			,s.total_physical_reads as 'Total_Physical_Reads'
			,s.total_logical_writes as 'Total_Logical_Writes'
		from sys.dm_exec_query_stats s
		cross apply sys.dm_exec_sql_text(s.sql_handle) as qt
		where s.sql_handle = @SQLHANDLE
		and s.statement_start_offset = @StartOffSET
		and s.statement_end_offset = @EndOffSET
	Fetch Next from CheckOps_TempDATA into @SQLHandle,@StartOffSet,@EndOffSet
End
Close CheckOps_TempDATA
Deallocate CheckOps_TempDATA