Post reply

DBCC command

  • May 11, 2011 at 12:59 am

    #239343

    Hi all,

    Im actually executing a while loop for updating a table. As the records are huge, it takes some 1 to 1 1/2 hours to complete

    My Question is:

    is there any DBCC command to know exactly which row the while loop is accessing in between the loop is executing?

    or any other ways to find the exact row that the while loop is accessing at any point?

    Regards,

    Vijayakumar N

  • May 11, 2011 at 2:04 am

    #1322782

    Why are you using a while loop? Surely a set-based update will be faster?

    You can query the locks help (sys.dm_tran_locks) though it will take some work to get that back to a row.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 11, 2011 at 4:34 am

    #1322846

    you can use audit and database audit specification on the required table,there is an audit available for the UPDATE only also for the specific user as well as schema

    http://msdn.microsoft.com/en-us/library/cc280472.aspx

    http://technet.microsoft.com/en-us/library/cc280404.aspx

    another option is EVENT_DATA()

    http://msdn.microsoft.com/en-us/library/ms173781.aspx

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

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

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