how to explore/ retrieve SQL Server Transaction Log information

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » how to explore/ retrieve SQL Server...

how to explore/ retrieve SQL Server Transaction Log information

Rate Topic

Display Mode

Topic Options

Author

Message

hasib.hasan

Posted Friday, September 14, 2012 9:41 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 4:19 AM
Points: 2, Visits: 3

Can anyone advise me on how to explore/ retrieve SQL Server Transaction Log information e.g. interprete record of table/ row update/ delete command, including the information of host computers and user IDs, timestamps etc.?
Or, Can anyone help me quoting any effective software that can do this on any previous transaction?
Or, Can anyone advise me whether "eVisie TransactioneXplorer" or similar software can do that?

My email:hasib.hasan@gmail.com

Post #1359772

GilaMonster

Posted Saturday, September 15, 2012 3:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 38,028, Visits: 30,334

The undocumented function to read the log is fn_dblog. It's a table-valued function (hence used in a select statement), takes two parameters, start LSN and end LSN, both can be null.

It's completely undocumented, so good luck interpreting it.
You won't find host names in there, it's a transaction log, not an audit log

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 #1359791

Grant Fritchey

Posted Monday, September 17, 2012 3:51 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 13,435, Visits: 25,277

You can use third party tools to explore the log. They work, but won't supply information that's not there as Gail points out. I think Apex SQL has a log explorer tool.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1360062

srivathsani-296624

Posted Tuesday, September 18, 2012 12:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:48 AM
Points: 102, Visits: 125

You can also use Lumigent Log explorer,SQL Log Rescue avialable in the market to explore the transaction logs.I have written an article regarding teh usage of this software.It can be accessed at http://www.sqlservercentral.com/articles/Product+Reviews/2846/

Post #1360573

« Prev Topic | Next Topic »

Permissions