June 15, 2009 at 3:42 pm
One of my Job was failing couple of times on its defined schedule and the reason for its failure according to the log event is tempdb log was full. When I checked which transaction is using the tempdb log using ' Select name, log_reuse_wait_desc From sys.databases' I see 'Active_Transaction' for the log_reuse_wait_desc column . I checked if there is any open transaction using 'DBCC Opentran('tempdb') I got the message that there is no open transaction in the tempdb database. Why is my tempdb log stuck with the 'Active_Transaction' ??
June 15, 2009 at 4:16 pm
I think if the transaction fails with a full tempdb log, it gets rolled back. It seems to me I've seen that written somewhere.
Can you make the job fail? It sounds like you don't have enough tempdb space set.
June 15, 2009 at 4:37 pm
Yes if the transaction fails it gets rolled back. But the question is after the job fails then it should roll back and the log_reuse column should have 'Nothing' as status and initial allocated space should be available with no open transaction. I am getting 'Active_Transaction' as Status . Why??
March 4, 2010 at 4:14 pm
I have the same problem. I thought I'd wait for my maintenance window where I had to restart the SQL Service thereby recreating TempDB. It still has Active_transaction there but DBCC Opentran('tempdb') shows no active open transactions....
what gives? I had a 16 gig Tempdb log file 😀
'nix
April 13, 2010 at 8:53 am
I noticed the same problem and also I did not find any active transaction, I made a SHRINK of TLogFile and magically returned to the field log_reuse_wait_desc NOTHING:w00t:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply