Introduction
Sleeping sessions with open transactions can lead to blocking and deadlocking issues. This is often due to coding problems, such as a stored procedure that fails to handle errors properly and ends without committing or rolling back the transaction. However, there can be other contributing factors as well.
Detection
The attached script utilizes four DMVs to gather information about sleeping sessions. The query returns a message that includes the SQL Handle of the most recently executed SQL statement, which can help identify the source of the issue in the code.
Custom Alert Definition
To create this alert, create a Custom SQL Multiple Numeric Return alert, paste in the attached SQL batch in the SQL Statement field and make it look similar to this:
