Save DBCC OPENTRAN result to a table or to a string

  • Hi all,

    I need to save "DBCC OPENTRAN" command result to a table column or to a string.

    Please reply me as early as possible with the possibilities that will helps me a lot.

  • Many of the DBCC statements have an undocumented option TABLERESULTS which changes the output of the statement from text to a resultset that can be inserted somewhere.

    With OPENTRAN, it'll be

    DBCC OPENTRAN WITH TABLERESULTS

    Wrap that inside an EXECUTE using dynamic SQL and you can insert it into a table. The table must have columns that match the DBCC output.

    So that'll be

    INSERT INTO SomeTable

    EXECUTE ('DBCC OPENTRAN WITH TABLERESULTS')

    The output from OPENTRAN with the tableresults option will look something like this:

    OLDACT_SPID52

    OLDACT_UID-1

    OLDACT_NAMEuser_transaction

    OLDACT_LSN(2071:33:1)

    OLDACT_STARTTIMEJan 11 2009 11:09:37:333AM

    OLDACT_SID0x0105000000000005150000002e86f8cbc457a001b905

    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
  • Hi,

    You can do something like this:

    CREATE table dbccOpenTran

    (

    DatabaseName sysname

    , OpenTran varchar(30)

    )

    insert into dbccopentran

    exec ('dbcc opentran with tableresults')

    Helpful?

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • Thank you very much for your quick reply.

    Really i am very grateful to u.......

  • Hi,

    I just realized you will not get a timestamp so you might want to do something like this instead:

    CREATE table dbccOpenTran

    (

    DatabaseName sysname

    , OpenTran varchar(30)

    , ntime datetime DEFAULT getdate()

    )

    insert into dbccopentran (databasename, opentran)

    exec ('dbcc opentran with tableresults')

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • Unless you can convert the sid to something usable how does that help?

Viewing 6 posts - 1 through 5 (of 5 total)

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