What is kept in transaction log and what is not?

Question

What is kept in transaction log and what is not?

AivarsH

Mr or Mrs. 500

Points: 597
More actions
November 1, 2011 at 6:06 am

#251040

Hello all,
Does transaction log stores some information about transactions that has been actually rolled back by server?
Is there a way to get some information about TSQL statements that were executed against server, but was rolled back for some reason?
Thanks in advance
Aivars

Viewing 12 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Aivars Herings (11/1/2011)
Hello all,
Does transaction log stores some information about transactions that has been actually rolled back by server?

Yes. Every single modification made to the database is logged and no log records are ever removed from the log, though they can be overwritten when the log wraps around.

Is there a way to get some information about TSQL statements that were executed against server, but was rolled back for some reason?

Kinda, but either expensive or very difficult. Log reader tools typically start around $1000 per license. You can read the log with fn_dblog, but it's not documented and not intuituve.

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

AivarsH Mr or Mrs. 500 Points: 597 More actions · Answer 2

GilaMonster (11/1/2011)

Aivars Herings (11/1/2011)
Kinda, but either expensive or very difficult. Log reader tools typically start around $1000 per license.

Yes, but this is solid investment. Can you recommend me some tool or vendor?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

First, what are you trying to do?

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

AivarsH Mr or Mrs. 500 Points: 597 More actions · Answer 4

How to say ... currently I am trying to understand who or what has modified data in one of databases I am responsible for.

Since this has happened in past, it is too late to set up any trace in SQL Profiler ... Therefore I'm looking for toll which would give me (as much as possible and in understandable way) information from transaction logs.

I remember that once, long time ago, I was using a tool from Lumigent. I cant 100% recall what was its name (Log explorer?), but it is discontinued now and latest version available is compatible only with SQL Server 2000. Would be just perfect to find something very similar to Lumigents' Log explorer.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Aivars Herings (11/1/2011)
How to say ... currently I am trying to understand who or what has modified data in one of databases I am responsible for.
Since this has happened in past, it is too late to set up any trace in SQL Profiler ... Therefore I'm looking for toll which would give me (as much as possible and in understandable way) information from transaction logs.

How long ago and what recovery model is the database in?

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

AivarsH Mr or Mrs. 500 Points: 597 More actions · Answer 6

Database is in full recovery mode since its creation (approximately 1 year).

I have daily full backups for last week + transaction log backups made each hour (also for a week)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Do you know when the data was modified?

A log reader might give you what was done (if it happened during the time you have log backups over), but it won't tell you who. Login name is not stored in the tran log, it's not necessary for rollback or database recovery

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

AivarsH Mr or Mrs. 500 Points: 597 More actions · Answer 8

Yep, I know it approximately +/- 15 minutes.

Actually, if I would see update statement, I could guess was it application or was it some of my teammates.

And returning to applications/tools for transaction log analysis - is there something that you would recommend to purchase?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Apex SQLLog is the one I know. Not cheap.

It won't give you the exact update statement as was run, because that's not logged. All that's logged is before and after data, so the tool can show you what changed, can give you an undo statement, but not the original piece of SQL that was run.

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

steveb. SSC-Forever Points: 46733 More actions · Answer 10

Apex SQLlog will also tell you who made the changes.

AivarsH Mr or Mrs. 500 Points: 597 More actions · Answer 11

Ok, understand.

I have extended my view on several things 🙂

Must say Thank You for educational discussion.