Post reply

Save DBCC OPENTRAN result to a table or to a string

  • January 11, 2009 at 12:42 am

    #204859

    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.

  • January 11, 2009 at 2:00 am

    #924964

    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_SPID 52

    OLDACT_UID -1

    OLDACT_NAME user_transaction

    OLDACT_LSN (2071:33:1)

    OLDACT_STARTTIME Jan 11 2009 11:09:37:333AM

    OLDACT_SID 0x0105000000000005150000002e86f8cbc457a001b905

    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

  • January 11, 2009 at 2:09 am

    #924968

    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

  • January 11, 2009 at 2:54 am

    #924971

    Thank you very much for your quick reply.

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

  • January 11, 2009 at 3:24 am

    #924973

    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

  • June 21, 2011 at 4:24 pm

    #1342167

    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