Post reply

DBCC Opentran says no active transactions but sp_WhoIsActive says 1 transaction open

  • April 24, 2018 at 4:08 pm

    #330523

    Hello experts,

    I ran sp_WhoISActive and see several rows with the value 1 in the open_tran_cpunt column.
    However, when I run DBCC OPENTRAN, it says:

    "No active open transactions."
    DBCC execution completed. If DBCC printed error messages, contact your system administrator."

    Does anyone know why the results seem to contradict each other?
    Thanks for any help.
    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • April 24, 2018 at 5:30 pm

    #1988134

    webrunner - Tuesday, April 24, 2018 4:08 PM

    Hello experts,

    I ran sp_WhoISActive and see several rows with the value 1 in the open_tran_cpunt column.
    However, when I run DBCC OPENTRAN, it says:

    "No active open transactions."
    DBCC execution completed. If DBCC printed error messages, contact your system administrator."

    Does anyone know why the results seem to contradict each other?
    Thanks for any help.
    - webrunner

    DBCC OPENTRAN reports open trans for the database specified. WhoIsActive likely using one of the DMVs looking for transactions on the instance. 
    I would guess there was an open transaction in another database than you specified with DBCC OPENTRAN (it uses the current database where executed if you didn't specify a database).  

    Sue

  • April 24, 2018 at 5:38 pm

    #1988135

    Sue_H - Tuesday, April 24, 2018 5:30 PM

    webrunner - Tuesday, April 24, 2018 4:08 PM

    Hello experts,

    I ran sp_WhoISActive and see several rows with the value 1 in the open_tran_cpunt column.
    However, when I run DBCC OPENTRAN, it says:

    "No active open transactions."
    DBCC execution completed. If DBCC printed error messages, contact your system administrator."

    Does anyone know why the results seem to contradict each other?
    Thanks for any help.
    - webrunner

    DBCC OPENTRAN reports open trans for the database specified. WhoIsActive likely using one of the DMVs looking for transactions on the instance. 
    I would guess there was an open transaction in another database than you specified with DBCC OPENTRAN (it uses the current database where executed if you didn't specify a database).  

    Sue

    D'Oh! Thanks, for some reason I didn't think of that. Explains what I saw.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply