How to view transaction logs in SQL Server 2008 [closed]

Question

I need to view the transaction logs of a database on SQL Server 2008 in order to find a delete transaction and hopefully roll it back.

Unfortunately I have no clue where to start, and I'm finding it difficult to determine which are good articles on Google.

What should I do?

Answer 1

You could use the undocumented

DBCC LOG(databasename, typeofoutput) 

where typeofoutput:

0: Return only the minimum of information for each operation -- the operation, its context and the transaction ID. (Default) 1: As 0, but also retrieve any flags and the log record length. 2: As 1, but also retrieve the object name, index name, page ID and slot ID. 3: Full informational dump of each operation. 4: As 3 but includes a hex dump of the current transaction log row. 

For example, DBCC LOG(database, 1)

You could also try fn_dblog.

For rolling back a transaction using the transaction log I would take a look at Stack Overflow post Rollback transaction using transaction log.

Answer 2

You can't read the transaction log file easily because that's not properly documented. There are basically two ways to do this. Using undocumented or semi-documented database functions or using third-party tools.

Note: This only makes sense if your database is in full recovery mode.

SQL Functions:

DBCC LOG and fn_dblog - more details here and here.

Third-party tools:

Toad for SQL Server and ApexSQL Log.

You can also check out several other topics where this was discussed:

• Rollback transaction using transaction log

• SQL Server Transaction Log Explorer/Analyzer

• Read the log file (*.LDF) in SQL Server 2008