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=sql-server-ver15
- 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!
Updated Jan 27, 2020
Version 3.0
Jose_Manuel_Jurado
Microsoft
Microsoft
