Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reading TRANSACTION LOG Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 4:16 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 180, Visits: 533
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
Posted Tuesday, October 23, 2012 5:05 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:23 AM
Points: 115, Visits: 400
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
Posted Tuesday, October 23, 2012 7:10 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 180, Visits: 533
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
Posted Tuesday, October 23, 2012 8:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1376298
Posted Wednesday, October 24, 2012 1:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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
Posted Wednesday, October 24, 2012 5:01 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 8:00 AM
Points: 709, Visits: 1,433
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
Posted Wednesday, October 24, 2012 3:02 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 180, Visits: 533
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.
Post #1376688
Posted Wednesday, October 24, 2012 5:23 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 8:00 AM
Points: 709, Visits: 1,433
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 »

Add to briefcase

Permissions Expand / Collapse