Accedi
 
HomeLibraryRisorseFormazioneDownloadSupportoCommunityForumBlog
Risorse per professionisti IT >
> 
> 
How to find out who is opening a transaction

Con risposta How to find out who is opening a transaction

  • martedì 1 maggio 2012 13:10
     
     
    Registrati per votare
    0

    Hello,

    I have an issue where DBCC OPENTRAN will show a long running transaction but I can not figure out who started that transaction. What events I need to capture in Profiler to find exact time and details about somebody using "BEGIN TRAN" or impicit transaction.

    Thanks,

    G

     

Tutte le risposte

  • martedì 1 maggio 2012 13:36
    Postatore
     
     
    Registrati per votare
    0

    Use Adam's great utility for such things  

    http://sqlblog.com/blogs/adam_machanic/archive/2011/04/27/who-is-active-v11-00-a-month-of-activity-monitoring-part-27-of-30.aspx

    /*BLOCKING*/
    EXEC dbo.sp_WhoIsActive @find_block_leaders =1,
    @output_column_list ='[blocked_session_count][blocking_session_id][session_id][CPU][status][wait_info]
                          [database_name][sql_text][host_name][open_tran_count]',
    @sort_order='[blocked_session_count]DESC'

    /*CPU*/
    EXEC dbo.sp_WhoIsActive
    @get_transaction_info=0,
    @output_column_list ='[session_id][start_time]
                      [cpu][status][context_switches][wait_info][program_name]
                     [database_name][sql_text][host_name][open_tran_count]', 
    @sort_order='[CPU]DESC'

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

     
  • martedì 1 maggio 2012 13:39
     
     
    Registrati per votare
    0
    I don't need to know what is causing blocking or anything like that. I just need to know inside profiler when transaction was created in first place. I'm not troubleshooting blocking or slow response, I troubleshoot unintended transactions being created.
     
  • martedì 1 maggio 2012 13:43
    Postatore
     
      Contiene codice
    Registrati per votare
    0

    Well, add text LIKE '%BEGIN TRAN%' in text column and search it.

    select * from sysprocesses where open_tran > 0
    SELECT* FROM SYS.SYSPROCESSES SP
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.[SQL_HANDLE])AS DEST WHERE OPEN_TRAN=1

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


     
  • martedì 1 maggio 2012 13:44
     
      Contiene codice
    Registrati per votare
    0

    Well, add text LIKE '%BEGIN TRAN%' in text column and search it.

    select * from sysprocesses where open_tran > 0

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Which events I need to capture in profiler to see it's happening. It's also can be set by SET IMPLICIT_TRANSACTIONS ON as well during connection establishment, so it's I think trickier then that.
     
  • martedì 1 maggio 2012 13:47
    Postatore
     
     Con risposta
    Registrati per votare
    0

    SQL:BatchStarting/completed

    SQL:STMTstatrting/completed

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Contrassegnato come risposta Lorry Craig martedì 1 maggio 2012 13:55
    •