Post reply

Reading TRANSACTION LOG

  • October 23, 2012 at 4:16 pm

    #284029

    Hello guys,

    I am new users of this forum

    I have question,

    in my company we had problem that one of program couldnt create/insert data to table, because there was problem with duplicate row in column with PK, so the transaction was rollback ?

    is possible with some easy way read transaction log ? or I need to have any program? Please can you send me some link for this? we have sql server 2008, i found lot of link and lot of query, but i cannot find what i want...

    here is what i need again..

    search changing in real time in transaction log or old time...

    I tried Apex, but if I inserted, updated or deleted from the table, the Apex didnt show somethink, after refresh was same....

    So do you have any idea ?

    thank you for you response

  • October 23, 2012 at 5:05 pm

    #1552106

    you can read the log using "fn_dblog" function

    but i didn't understand why u would need that

    Pooyan

  • October 23, 2012 at 7:10 pm

    #1552113

    thx for response, i tried it, but it very difficult for reading, i would like something easier

    ..

    i need this because my boss wants it for better and faster solution of problem

    for example, who,what and which column updated in this table before problem .. understand me ?

    Sometimes is problem that users/programs update/insert wrong data and nobody say "I updated/inserted"

    Is possible or not ??? Or is another way for check move in specific table ?

  • October 23, 2012 at 8:19 pm

    #1552119

    I've never tried it and I sure don't know if it'll work for data that's been rolled back, but have a look at the following...

    http://raresql.com/2012/04/08/how-to-recover-truncated-data-from-sql-server-without-backup/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 24, 2012 at 1:18 am

    #1552147

    There's no easy way to read the tran log. You can use fn_dblog or you can buy a log reader (~$1000). Note for either to be useful the transaction you're trying to read needs to be part of the active portion of the log (no checkpoint/log backup since)

    What are you trying to do anyway?

    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

  • October 24, 2012 at 5:01 am

    #1552207

    It may be best to setup a profiler trace to include the SQL statement and user errors to see if you can capture what is being passed at the time the error is thrown.

    Just make sure it is a server-side trace. ESPECIALLY for a production instance.

    Joie Andrew
    "Since 1982"

  • October 24, 2012 at 3:02 pm

    #1552441

    thanks guys for response.

    I think that no solution for this. And we will be carefull on manipulation with data ( especially one user ) 🙂

    and my boss told me about Oracle that it has some tool for reading logs. Is right ? I have skill just with MSQL.

  • October 24, 2012 at 5:23 pm

    #1552478

    Oracle has the log miner tool, but that will only work on an Oracle database. Also, it is reading the redo logs, so that would be a transaction that is committed. You would basically get the same error as you have here. This is not mentioning the fact that you would have to first port this application over to Oracle (assuming it is supported) and then wait for the error to occur before being able to test it.

    I would not say that there is no solution. You have plenty of solutions in SQL Server to consider such as:

    - Setting up a trace to monitor activity/statements and capture the offending statement

    - Setup a DML trigger to capture the data you want and to report it back to you

    - Possibly setup an Extended Event to do what you are looking for. I have little knowledge with Extended Events though, so I am unsure if it would be able to do what you are looking for

    - Setup database auditing

    If it is an important matter to your business it is worth researching more before declaring that it cannot be done.

    My two cents.

    Joie Andrew
    "Since 1982"

Viewing 8 posts - 1 through 7 (of 7 total)

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