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 ««123»»

Temp db log file is growing . Expand / Collapse
Author
Message
Posted Thursday, August 30, 2012 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 5, 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
Posted Sunday, September 2, 2012 8:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:30 AM
Points: 40, Visits: 328
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
Posted Sunday, September 2, 2012 10:23 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 1,613, Visits: 1,538
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
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1353325
Posted Monday, September 3, 2012 12:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 2:15 AM
Points: 120, Visits: 131
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
Posted Monday, September 3, 2012 4:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:55 AM
Points: 18, Visits: 444
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
Posted Monday, September 3, 2012 5:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 42,495, Visits: 35,566
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
Posted Tuesday, September 4, 2012 8:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:45 PM
Points: 195, Visits: 360
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
Posted Tuesday, September 4, 2012 7:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:51 AM
Points: 89, Visits: 878
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
Posted Wednesday, September 5, 2012 3:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:37 AM
Points: 40, Visits: 67
try restarting SQL Server Agent (MSSQLSERVER) from services.msc
Post #1354404
Posted Wednesday, September 5, 2012 3:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 26, 2014 8:49 AM
Points: 2,276, Visits: 779
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 »

Add to briefcase ««123»»

Permissions Expand / Collapse