How to view transaction logs in SQL Server 2008

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

User · Answer

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.

User · Answer

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 nbsp Overflow post Rollback transaction using transaction log

User · Answer

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

[sql-server] How to view transaction logs in SQL Server 2008

Examples related to sql-server

Examples related to transaction-log