Reading TRANSACTION LOG

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Reading TRANSACTION LOG

Reading TRANSACTION LOG

Rate Topic

Display Mode

Topic Options

Author

Message

tony28

Posted Tuesday, October 23, 2012 4:16 PM

Valued Member

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 60, Visits: 169

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

Post #1376274

pooyan_pdm

Posted Tuesday, October 23, 2012 5:05 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 4:48 PM
Points: 108, Visits: 360

you can read the log using "fn_dblog" function
but i didn't understand why u would need that

Cheers ,
Pooyan D
________________________________________________
Microsoft Certified Technology Specialist : SQL Server 2008

Post #1376283

tony28

Posted Tuesday, October 23, 2012 7:10 PM

Valued Member

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 60, Visits: 169

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 ?

Post #1376291

Jeff Moden

Posted Tuesday, October 23, 2012 8:19 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:33 PM
Points: 32,889, Visits: 26,757

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1376298

GilaMonster

Posted Wednesday, October 24, 2012 1:18 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 37,637, Visits: 29,889

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 2008, MVP
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

Post #1376338

Joie Andrew

Posted Wednesday, October 24, 2012 5:01 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 AM
Points: 535, Visits: 1,010

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"

Post #1376409

tony28

Posted Wednesday, October 24, 2012 3:02 PM

Valued Member

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 60, Visits: 169

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

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

Post #1376688

Joie Andrew

Posted Wednesday, October 24, 2012 5:23 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 06, 2013 5:58 AM
Points: 535, Visits: 1,010

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"

Post #1376732

« Prev Topic | Next Topic »

Permissions