TempDB tran log growing slowly - yet we are in simple mode! -- sql server (alternate)

sql server (alternate) : TempDB tran log growing slowly - yet we are in simple mode!

trhorner NO[at]SPAM att.net
2/27/2004 5:34:58 AM

Something strange is happening to our SQL Server DB (2000). The
tempdb transaction log file continues to grow (quite slowly) for no
apparent reason. We have it in simple mode, and I have tried a manual
checkpoint command and manual shrink (of the log file only). There
are no unusual SQL's (large or small) going on. A "heavy hitter" would
make it grow fast, not 10 MB every 30 minutes or so. This server has
been in production for over a year with no similar issues to this.

Anyone encounter a similar situation? This started (as far as we can
tell) sometime yesterday. It is growing about 200 MB a day, and is up
to 600 MB now, with all but 8 MB "used". For now we added space, but
of course that is not a long term solution. The two "data" files
(each had a 200 MB initial allocation) have never gone much above 100
MB each used (they are each about 100 MB now and have been that way
for several days, but have shrank and grown). There are about 30
small to medium sized "regular" databases on this instance.

Any help would be appreciated, especially if there is a way we can fix
this without bouncing the engine (I know, wishful thinking...). The OS
is Win 2000, SP3. SQL Server is at 2000, SP 3a.

manrajshekar NO[at]SPAM yahoo.com
2/27/2004 3:08:18 PM

Transact-SQL constructs such as GROUP BY, ORDER BY DESC, and so forth,
will automatically require tempdb for space. This will also cause an
implicit BEGIN TRANSACTION record in tempdb for the space. This tempdb
transaction will continue for the duration of the transaction in the
user db, which can defer tempdb log truncation for this period. If the
transaction in the user db is halted for any reason, including a
blocking lock the transaction in tempdb will likewise be left open,
preventing tempdb log truncation. Debug the application and/or resolve
the concurrency issues which cause this. Check the current activity on
the Server.. And also notice if there are any open transactions for a
long time, which is not allowing truncating on the user DB indirectly
affecting tempDB log.

Regards,

Simon Hayes
2/27/2004 6:50:37 PM

[quoted text, click to view]

These KB articles might he helpful:

http://support.microsoft.com/default.aspx?scid=kb;en-us;272318&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;317375&Product=sql2k

Even using the simple recovery model, the file will not physically shrink
unless you do it manually or turn on autoshrink, so make sure you are
distinguishing between truncating the log (frees up space in the log file)
and shrinking it (reduces the physical file size). Also check out "Shrinking
the Transaction Log" in Books Online, which describes possible issues with
virtual logs preventing you shrinking the file.

Simon

Erland Sommarskog
2/27/2004 11:05:27 PM

tom horner (trhorner@att.net) writes:
[quoted text, click to view]

Sounds to me like there is a an open transaction. SQL Server cannot
truncate the transaction log, beyond the point of the oldest current
transaction.

DBCC OPENTRAN in tempdb, and possibly all other databases as well, should
track down the culprit.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

View Other Months
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007

home · blog · groups

Questions? Comments? Contact the dn