Temp db log file is growing .

Question

Temp db log file is growing .

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Assuming there is an inactive portion, assuming the inactive portion is at the end of the log file and assuming that the shrink does not cause problems when used on a live, in-use TempDB (see Robert's comment)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

B's-Data SSChasing Mays Points: 607 More actions · Answer 2

Sounds to me like you have a quarterly job that is running a really large transcation running. Are you able to add a second SAN drive? Perhaps you could do this and add a .ndf file to the tempdb which would help with the space.

You really need to determine what is the cause of the massive grown.

Lee Linares SSCrazy Points: 2723 More actions · Answer 3

Depending on how long ago tempdb physically grew you may be able to query the default trace file if you have it enable. It is enabled by default. It captures among other events, the auto-growths of the database files and transaction log files. It may show who was running the transaction that caused the physical file to grow. If the default trace does not go back far enough then you will have to wait until after you have reclaimed the tempdb and wait for it to happen again. There is an excellent article with code on Simple-Talk for querying the default trace file.

http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/?utm_source=simpletalk&utm_medium=email-main&utm_content=DefaultTrace-20110321&utm_campaign=SQL

If you want to take a heavy handed approach you restrict the max size of the tempdb database. When the guilty transaction fills it up without finishing the transaction will eventually timeout and rollback which SHOULD release the the internal space (but NOT the physical space). I don't recommend this approach but....

Lee

makrandghanekar SSC Rookie Points: 48 More actions · Answer 4

try restarting SQL Server Agent (MSSQLSERVER) from services.msc

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

sounds like checkpointing may have stopped - you get that a lot on slow IO when you have large amounts of memory

try the following

USE TEMPDB

GO

CHECKPOINT

if the script takes more than a few seconds then you can just cancel it and it should al least clear the inactive portion of the log

MVDBA

durai nagarajan SSCoach Points: 15775 More actions · Answer 6

makrandghanekar (9/5/2012)
try restarting SQL Server Agent (MSSQLSERVER) from services.msc

what do you think this will do?

Regards
Durai Nagarajan

2ndHelping SSC-Addicted Points: 405 More actions · Answer 7

Maybe this will help you identify the source of the tempdb log file usage; although indirectly because it's looking more at tempdb objects

/** tempdb usage by task **/

SELECT distinct t1.session_id, login_name, s.login_time, s.last_request_start_time, s.last_request_end_time, 'dbcc inputbuffer(' + cast(t1.session_id as varchar) + ')',

'exec sp_who2 ' + cast(t1.session_id as varchar),

s.host_name,

(t1.internal_objects_alloc_page_count + task_alloc) as allocated,

(t1.internal_objects_dealloc_page_count + task_dealloc) as

deallocated

from sys.dm_db_session_space_usage as t1

inner join

(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

on t1.session_id = t2.session_id and t2.session_id >50

inner join sys.dm_exec_sessions s on t1.session_id = s.session_id

order by allocated DESC

/** tempdb usage by current running request **/

--- Tempdb - statements using tempdb

---

--- This script is provided "AS IS" with no warranties, and confers no rights.

--- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm

SELECT t1.session_id,

(t1.internal_objects_alloc_page_count + task_alloc) as allocated,

(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated

, t3.sql_handle, t3.statement_start_offset

, t3.statement_end_offset, t3.plan_handle

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

order by allocated DESC

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 8

Hello tapaskumardm,

you are using replication. What does

dbcc opentran

get you? Any not distributed transactions?

Regards

karl

Best regards
karl