TempDB tran log growing slowly - yet we are in simple mode!

[tom horner]

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.

THANKS IN ADVANCE!

[Simon Hayes]

"tom horner" <tr******@att.net> wrote in message
news:57*************************@posting.google.co m...

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.

THANKS IN ADVANCE!

These KB articles might he helpful:

http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...&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]

tom horner (tr******@att.net) writes:

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.

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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Manoj Rajshekar]

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,
-Manoj Rajshekar