So we're running into an issues where TempDB's log is currently locked up. After checking log_reuse_wait_desc, we see that it is locked from an active transaction. Now the weird part is that when we run DBCC OPENTRAN to get information on the query causing the lock, the command returns "No active open transactions".
We've killed every spid connecting to TempDB and we're at the point where we'll be cycling the instance to resolve the issue.
Does anyone understand what's happening here or if there's a better way to deal with this?
SQL Server 2012 – Locked TempDB Log Solutions
sql serversql-server-2012tempdb
Related Question
- Sql-server – Moving TempDB data and log files
- Sql-server – SQL Deadlock on the same exclusively locked clustered Key (with NHibernate) on delete / insert
- Sql-server – SQL Server High Availability and Sharepoint – log file grows when no database activity
- Sql-server – Potential corruption in sys.sysdercv, latch errors from ghost cleanup process
- SQL Server – Can WITH XMLNAMESPACES Trigger a DDL Database Trigger?
Best Answer
So we figured it out:
We rebooted the server and when it came back up, we were still having issues. We started re-considering whether it was just an issue with TempDB and turned to maybe trying to catch the issues with extended events. While looking into it, we stumbled across an extended event that nobody knew about called "Testing." We disabled that XE and, go figure, everything started working fine again. I'm not sure WHY this locked up everything in TempDB and we're looking into understanding the issue, but for now - the issue is resolved.