Lesson Learned #121: The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'

[Jose_Manuel_Jurado]

We have been working on an issue that our customer reported this error message - The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'

It is very important to know that this type of issue is based on an user query that is executing and we need to find out what is the query that is causing this.

As we reported in our previous article you could identify the query using this recomendation, also, please, review the article about long transaction

Additionally, we would like to share with you other queries that could help you in this tracking process:

• Use dbcc opentran it will return any open transactions that would also cause the Tempdb to be full, for more information see https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-opentran-transact-sql?view...
• Creating an alert on the tempdb to send a notification when the Tempdb percentage exceed a specific the threshold to start the monitoring process by going through the scripts provided previously. For more details regarding the Alert see https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-metric

Enjoy!