April 19, 2018 at 2:18 am
Hello,
We are experiencing a strange issue:. Many times per day, some transactions simply seem to get frozen.
We are monitoring them every 5 seconds with the system view "sys.dm_tran_active_transactions" joined with "sys.dm_tran_database_transactions" to retrieve more information.
The affected transactions are associated to INSERT sentences, and they remain in these states for until 30 minutes:
* sys.dm_tran_active_transactions -> transaction_state 2: "The transaction is active".
* sys.dm_tran_database_transactions -> transaction_state 3: "The transaction has been initialized but has not generated any log records".
In the meanwhile, the query handle field in the sys.dm_exec_requests system view is null, so it doesn't seem any query is executing.
So the transaction is running but it has done nothing.
Nevertheless, the system view "sys.dm_os_waiting_tasks" doesn't show the task to be waiting for any resource while the transactions are apparently "frozen".
Any help about a possible explanation will be welcome.
Regards,
Ignacio
April 19, 2018 at 2:39 am
Ignacio
Do you have sp_whoisactive on your server? You can use it to see current activity all at once, without having to query several DMVs.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply