Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Tempgb log - not able to shrink....none of the queries are helping Expand / Collapse
Author
Message
Posted Thursday, November 01, 2012 2:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 01, 2013 10:17 AM
Points: 323, Visits: 984
i dont think so...

he is asking about tempdb log file not data file.

by restricting log file it will get reuse if there any inactive portion at the start of the log file.
also you can add extra log file to the tempdb


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1379667
Posted Thursday, November 01, 2012 7:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
yes, my miss.

Regards
Durai Nagarajan
Post #1379823
Posted Friday, November 02, 2012 11:08 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 734, Visits: 3,561
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



______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor
Post #1380517
Posted Friday, November 02, 2012 2:36 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 3:44 PM
Points: 1,266, Visits: 2,912
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




Thanks for the info. I think my post took a different angle now . My goal here is to find the queries which are filling up my tempdb. In your first query it ONLY tells how data file, what about log file? How do i tell freespace and total space in logfile? Why do i need to clear cache to shrink tempdb? In last query i won't be able to find the query using most space.
Post #1380592
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse