SQLQuest29 (11/2/2012)

---

sqldba_newbie (10/30/2012)

---

I have gone through many many different forums in trying to identify my tempdb issues. None of the queries are giving me the data i want. Basically at the moment my tempdb log file is 38 gb, after i run all the queries from different forums i see ONLY 300 MB being used in tempdb. I am still lost and have these questions:

i) Do the DMV's consider log file usage/capacity at all or is it just data file?

ii) Can someone please provide me with a query which will tell me which spid has used up all the tempdb data and log file.

Thanks

As Gail mentioned .. why are you shrinking TEMPDB ? what is causing it to bloat ?

Below is some T-SQL that will help you.

-- 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