How to get previous statements in a batch?

• SQL Rob

  Right there with Babe

  Points: 748

  More actions

  February 25, 2015 at 7:25 pm

  #314970

  Hi all,

  I have an interesting problem. A number of spids are being blocked by a single select statement. The select statement is the same as returned from sp_who2, sysprocesses, sp_whoisactive of dbcc inputbuffer. It is not waiting on anything and has status as sleeping.

  Clearly it is not 'just' a sleeping select statement as I can see over a thousand locks held by the spid on 2 user databases and tempdb. I'm working on the theory there is a begin transaction with a bunch of statements and no closing commit. But I want to be able to prove that. How can I show what statements were previously executed as part of this transaction?

  Thanks.

  Additional Info: SQL 2012 Enterprise Edition. This is a test server but is a reproduction of a live issue. At this point the application team cannot isolate the code causing the problem, only the set of processes the code resides in.

• Grant Fritchey

  SSC Guru

  Points: 399183

  More actions

  February 26, 2015 at 4:25 am

  #1780172

  If the text isn't visible from sys.dm_exec_sql_text, then it's gone. But, you can prove it's an open transaction by looking at sys.dm_tran_active_transactions.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• SQL Rob

  Right there with Babe

  Points: 748

  More actions

  February 26, 2015 at 1:28 pm

  #1780390

  Thanks Grant, that's what I was hoping wasn't the case, but suspected was.