Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tempgb log - not able to shrink....none of the queries are helping


Tempgb log - not able to shrink....none of the queries are helping

Author
Message
sanket kokane
sanket kokane
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 1019
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

-----------------------------------------------------------------------------
संकेत कोकणे
durai nagarajan
durai nagarajan
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1107 Visits: 2771
yes, my miss.

Regards
Durai Nagarajan
SQLQuest29
SQLQuest29
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 4310
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 :-)
curious_sqldba
curious_sqldba
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1543 Visits: 3573
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 Sad. 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search