Temp db log file is growing .

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Temp db log file is growing .

23 posts, Page 2 of 3

««1 2 3 »»

Temp db log file is growing .

Rate Topic

Display Mode

Topic Options

Author

Message

btaylor 78431

Posted Thursday, August 30, 2012 7:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 05, 2012 7:48 AM
Points: 3, Visits: 18

TempDB automatically re-uses space. So, if the transaction log continues to grow it is because it has rows which may not be deleted due to open transactions.

I have found that using connection pooling in ADO if you don't close your connection, the connection pool won't complete implicit transactions. This results in SQL Transactions that remain. Until the transaction is committed, or rolled back, the transaction log cannot re-use this space.

Since you say this only happens once a quarter I would guess the ADO issue may not be the cause. You may have a longer running job requiring more time and thus grows tempdb transaction log.

If you find this is the case, then consider taking work for larger jobs and breaking them down into smaller batches.

Finally, make sure CheckPoint is at a short enough interval. This is what causes SQL Server to go and remove completed transactions from transaction logs of databases in simple recovery mode. TempDB is always in simple recovery mode.

Post #1352224

T2000

Posted Sunday, September 02, 2012 8:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 05, 2013 11:37 AM
Points: 38, Visits: 300

What I do is create a 2 or 5Gb dummy file on each SQL disk (ldf,mdf,tmpdb). Whenever there is an urgent disk storage problem I can delete the dummy file and buy myself some time. It is no final solution but it helped me out some a few time.

Post #1353222

Robert Davis

Posted Sunday, September 02, 2012 10:23 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 7:27 PM
Points: 1,559, Visits: 1,398

durai nagarajan (8/29/2012)

any problem in using DBCC SHRINKFILE?

Yes, using DBCC Shrink[File|Database] on a live tempdb has in some cases caused corruption.

My blog: SQL Soldier
Twitter: @SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring

Post #1353325

prasanna.vinay

Posted Monday, September 03, 2012 12:14 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:39 AM
Points: 120, Visits: 71

Hi,

In your case you can use DBCC SHRINKFILE if it doesn't worked out try once again, then look for log space if the reduction is not much satisfied then use the query
select name,log_reuse_wait_desc form sys.databases where dbname='<dbname>' look for the rows that query returns. if it is active transaction identify the spid and troubleshoot or kill the transaction. If you dont have permission to kill then you can use DBCC FREEPROCCACHE---> this will release the plan cache in the memory and gives some space

Post #1353355

db_expert_pradeep

Posted Monday, September 03, 2012 4:41 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 2:06 AM
Points: 18, Visits: 378

I think DBCC SHRINKFILE is worth to use.
It will make some space because in log file there is active and inactive portion.You can reclaim inactive space in log file.

Post #1353439

GilaMonster

Posted Monday, September 03, 2012 5:28 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

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 2008, MVP
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

Post #1353461

bgdjwoods

Posted Tuesday, September 04, 2012 8:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:42 AM
Points: 100, Visits: 266

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.

Post #1353957

Lee Linares

Posted Tuesday, September 04, 2012 7:57 PM

Valued Member

Group: General Forum Members
Last Login: Today @ 3:26 PM
Points: 70, Visits: 591

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

Post #1354298

makrandghanekar

Posted Wednesday, September 05, 2012 3:39 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 9:31 PM
Points: 40, Visits: 64

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

Post #1354404

MVDBA

Posted Wednesday, September 05, 2012 3:45 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

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

Post #1354410

« Prev Topic | Next Topic »

23 posts, Page 2 of 3

««1 2 3 »»

Permissions