SQL Server Tempdb LOG file growing
On a SQL Server 2000 system, I have a templog.ldf file that seems to grow without bound! But when I check, there are never any open transaction in the tempdb (using DBCC OPENTRAN), nor do I ever use explicit transactions within tempdb.
I do, however, use temp tables within my stored procedures. And who knows what ASP.NET data access objects might do behind the scenes? The database backs an ASP.NET web site.
Regardless, the tempdb.mdf file -- the tempdb data file -- is at a quite reasonable 700 MB. The templog.ldf file can grow to 30 GB in a day! Probably more if I didn't run out of disk space. I do not explicitly drop the #tables in the stored procs, but again, the mdf file never seems to grow very large.
Why would the transaction log grow when there are no outstanding transactions? Does the transaction log ever reclaim space? Also DUMP TRAN WITH NO_LOG does not shrink the file, nor does a CHECKPOINT, event through trunc. log on chkpt is enabled on tempdb. Stopping the SQL Server clears the tempdb (and probably DBCC SHRINKFILE would but I have not done that).
Thanks for your help!
Answer
read these:
Truncating the Transaction Log
pay attention to: Log truncation always occurs on a checkpoint in tempdb.
http://technet.microsoft.com/en-us/library/aa174538(SQL.80).aspx
Optimizing tempdb Performance
http://technet.microsoft.com/en-us/library/aa178421(SQL.80).aspx
My templog.ldf is huge (45gb), What if anything should I do? https://serverfault.com/questions/61479/my-templog-ldf-is-huge-45gb-what-if-anything-should-i-do