We have a problem with our Power Builder application. It has pages that open a transaction and then don't close it until the user has either completed a certain operation or has logged off. The problem is that by the time we get alerted to the blocking, <= 60 seconds, the user might have moved on to another page. So the query that shows up as being the blocker is not necessarily the troublemaker. Any suggestions on how to "trace back" to the original query? Or is that even possible? We're running MS-SQL 2012. Thanks
Put a server side trace and/or an extended events session on to try to capture that since it's off hours.You can then correlate the time that the command was executed to the output of the trace/events to find out what T-SQL was being executed.
Thanks. Again, we have traces in place, but they check for clocking every 60 seconds. However, maybe if we caught every begin tran event we'd be able to correlate it! Thanks. (Sorry John M, I don't know how to mark your comment as the answer.)