Search code examples
sql-servertransaction-log

How to view transaction logs in SQL Server 2008

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?

Solution

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