0

We saw some substantial log growth on both tempdb and one of the user databases. Our database is in Snapshot isolation and we take transaction log backups every 15 minute.

First 'TL log full error 9002' happened at 12.03, 2 minutes after the tl backups were taken. TLs of both the database and the tempdb stated to grow at this point. I think most of the transactions resulted in the error (in between all the scheduled jobs like reindexing and some other jobs that processes data are successfully completed).

Error log entered over 8 million entries of Error 9002 for the next 12 hours till the tl log size reached the max filesize). I go through all the error logs both sql and windows-which doesn't show any information other than the error messages "The transaction log for database 'abcd' is full due to 'ACTIVE_TRANSACTION'.".

I appreciate if someone can guide me in the proper direction to find the culprit.

Improve this question
2
  • Use sp_who or sp_who2 to see if there is an active long running transaction, maybe someone forgot to COMMIT TRAN Commented Jan 20, 2019 at 10:56
  • Shrink log files Commented Jan 20, 2019 at 10:58
Related questions
2
TempDB Log File Growth Using Global Temp Tables
7
SQL Server Tempdb LOG file growing
1
tempdb SQL Server locking
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.