How to view transaction logs in SQL Server 2008

139,049

Solution 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.

Solution 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:

Solution 3

I accidentally deleted a whole bunch of data in the wrong environment and this post was one of the first ones I found.

Because I was simultaneously panicking and searching for a solution, I went for the first thing I saw - ApexSQL Logs, which was $2000 which was an acceptable cost.

However, I've since found out that Toad for Sql Server can generate undo scripts from transaction logs and it is only $655.

Lastly, found an even cheaper option SysToolsGroup Log Analyzer and it is only $300.

139,049

Author by

109221793

Updated on July 09, 2022

Comments

109221793 almost 2 years

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?
- Vadzim about 9 years
  
  Related: dba.stackexchange.com/questions/31348/…
- Jason Clark over 8 years
  
  Read this blog and know how to use fn_dblog() function to find deleted records in SQL Server sqlserverlogexplorer.com/reading-sql-server-transaction-logs
- Channa over 4 years
  
  sqlshack.com/how-to-read-a-sql-server-transaction-log
109221793 over 13 years

Hi @kevchadders, I will try that. Thanks. to clarify, is it the last column that is the most recent?
glagarto over 13 years

Yes. run DBCC LOG(database, 1) and you should see the TransactionID (4th column) imcrement up in hex
Raven almost 8 years

SysToolsGroup Log Analyzer costs $300 for a personal license and you arent allowed to use it for commercial purpose for that you need a Business license and that costs $799.