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);
