How to View a Transaction Log?

1.  Native tools/functions in SQL Server

1)     The undocumented DBCC LOG command:

 

DBCC LOG([,{01234}])

·        0 - Basic log Information (default)

·        1 - Lengthy Info

·        2 - Very Length Info

·        3 – Detailed

·        4 - Full

2)     The undocumented function introduced in SQL Server 2005. This function is used to return active (or un-truncated) part of transaction log file.


fn_dblog({BeginLSN | NULL}, {EndLSN | NULL})

 

(See http://www.sqlservercentral.com/blogs/livingforsqlserver/2012/11/19/time-pass-with-transaction-log-part-3-fn-dblog/ for details)

 

3)     The undocumented fn_dump_dblog() can be used to even view the transaction log inside of a backup file.

 

(See http://www.sqlsoldier.com/wp/sqlserver/day19of31daysofdisasterrecoveryhowmuchlogcanabackuplog for details)

 

4)     Use trace flag 2537 for SQL Server 2005/2008 - Allows function ::fn_dblog function ::fn_dump_dblog to look inside all logs (not just the active log). This flag works in 2008 R2 and 2012 too.

 

-- Enable trace flag 2537 to see all log entries (active and inactive)

DBCC TraceOn(2537);

 

-- Lots of inactive log entries

Select *

From fn_dblog(null, null)

 

-- Turn Off the flag

DBCC TraceOff(2537);

 

2.  Free 3rd-party tool

1)      Internals Viewer for SQL Server (http://internalsviewer.codeplex.com/) for SQL Server 2005 and 2008. Tested on SQL Server 2005.

 

3.  Commercial 3rd-party tool

1)     ApexSQL Log by ApexSQL at http://www.apexsql.com/sql_tools_log.aspx