Post reply

DBCC COMMANDS

  • October 7, 2011 at 8:12 am

    #1391751

    nice easy question for the end of the week 🙂

  • October 7, 2011 at 8:36 am

    #1391785

    Thanks for the question. A good command to know.

  • October 7, 2011 at 9:41 am

    #1391841

    sys.dm_tran_active_transactions will. I'm not aware of a way to get the statement from that but by looking at the sys.dm_tran_loks you'll be able to see what objects the tran is locking. Maybe someone else knows how to get the statement for the transaction.

    This should do that for you. Needs some refinement, but you get the gist...

    -- Gets the SQL Text from the transaction id

    SELECT text,*

    FROM

    sys.dm_tran_active_transactions tat

    JOIN sys.dm_exec_requests er

    ON er.transaction_id = tat.transaction_id

    CROSS APPLY sys.dm_exec_sql_text(sql_handle)


    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • October 7, 2011 at 11:54 am

    #1391904

    Good basic question... 🙂

    ===========================================
    Better try and fail than not to try at all...

    Database Best Practices[/url]

    SQL Server Best Practices[/url]

  • October 7, 2011 at 12:37 pm

    #1391928

    s_osborne2 (10/7/2011)

    This should do that for you. Needs some refinement, but you get the gist...

    -- Gets the SQL Text from the transaction id

    SELECT text,*

    FROM

    sys.dm_tran_active_transactions tat

    JOIN sys.dm_exec_requests er

    ON er.transaction_id = tat.transaction_id

    CROSS APPLY sys.dm_exec_sql_text(sql_handle)

    That'll work if the transaction has an active request running. What about something like this:

    begin transaction

    update person set lastname = 'smith'

    waitfor delay '00:05'

    commit transaction

  • October 7, 2011 at 4:20 pm

    #1392000

    thanks - nice question to round out the week! 😎

  • October 10, 2011 at 2:00 am

    #1392282

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 17, 2011 at 2:44 am

    #1395187

    Thanks...

    Thanks

Viewing 8 posts - 16 through 22 (of 22 total)

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