November 1, 2011 at 6:06 am
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
November 1, 2011 at 8:28 am
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
November 1, 2011 at 8:56 am
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?
November 1, 2011 at 9:27 am
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
November 1, 2011 at 9:36 am
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.
November 1, 2011 at 9:39 am
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
November 1, 2011 at 9:42 am
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)
November 1, 2011 at 10:03 am
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
November 2, 2011 at 2:26 am
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?
November 2, 2011 at 2:42 am
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
November 2, 2011 at 3:17 am
Apex SQLlog will also tell you who made the changes.
November 2, 2011 at 4:30 am
Ok, understand.
I have extended my view on several things 🙂
Must say Thank You for educational discussion.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply