Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reading TRANSACTION LOG


Reading TRANSACTION LOG

Author
Message
tony28
tony28
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 889
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



How to post data/code on a forum to get the best help: Option 1 / Option 2
pooyan_pdm
pooyan_pdm
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 422
you can read the log using "fn_dblog" function
but i didn't understand why u would need that

Pooyan
tony28
tony28
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 889
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 ?



How to post data/code on a forum to get the best help: Option 1 / Option 2
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45121 Visits: 39921
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47261 Visits: 44392
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


Joie Andrew
Joie Andrew
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 1904
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"
tony28
tony28
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 889
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.



How to post data/code on a forum to get the best help: Option 1 / Option 2
Joie Andrew
Joie Andrew
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 1904
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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search