Reading Transaction Log in SQL

If you are using SQL Server 6.5 you could query the system table SYSLOGS, by using the following statement:

SELECT xactid AS TRAN_ID,
CASE op
WHEN 0 THEN 'BEGINXACT Start Transaction'
WHEN 1 THEN 'Sysindexes Change'
WHEN 2 THEN 'Not Used'
WHEN 3 THEN 'Not Used'
WHEN 4 THEN 'INSERT Insert Row'
WHEN 5 THEN 'DELETE Delete Row'
WHEN 6 THEN 'INSIND Deferred Update step 2 insert record'
WHEN 7 THEN 'IINSERT NC Index Insert'
WHEN 8 THEN 'IDELETE NC Index Delete'
WHEN 9 THEN 'MODIFY Modify Row'
WHEN 10 THEN 'NOOP'
WHEN 11 THEN 'INOOP Deferred Update step 1 insert record'
WHEN 12 THEN 'DNOOP Deferred Update step 1 delete record'
WHEN 13 THEN 'ALLOC Allocate Page'
WHEN 14 THEN 'DBNEXTID Allocate Next Object ID'
WHEN 15 THEN 'EXTENT Allocate Empty Extent'
WHEN 16 THEN 'SPLIT Page split'
WHEN 17 THEN 'CHECKPOINT'
WHEN 18 THEN 'SAVEXACT Savepoint'
WHEN 19 THEN 'CMD'
WHEN 20 THEN 'DEXTENT Deallocate extent'
WHEN 21 THEN 'DEALLOC Deallocate page'
WHEN 22 THEN 'DROPEXTS Delete all extents on alloc pg'
WHEN 23 THEN 'AEXTENT Alloc extent - mark all pgs used'
WHEN 24 THEN 'SALLOC Alloc new page for split'
WHEN 25 THEN 'Change to Sysindexes'
WHEN 26 THEN 'Not Used'
WHEN 27 THEN 'SORT Sort allocations'
WHEN 28 THEN 'SODEALLOC Related to sort allocations'
WHEN 29 THEN 'ALTDB Alter database record'
WHEN 30 THEN 'ENDXACT End Transaction'
WHEN 31 THEN 'SORTTS Related to sort allocations'
WHEN 32 THEN 'TEXT Log record of direct TEXT insert'
WHEN 33 THEN 'INOOPTEXT Log record for deferred TEXT insert'
WHEN 34 THEN 'DNOOPTEXT Log record for deferred TEXT delete'
WHEN 35 THEN 'INSINDTEXT Indirrect insert log record'
WHEN 36 THEN 'TEXTDELETE Delete text log record'
WHEN 37 THEN 'SORTEDSPLIT Used for sorted splits'
WHEN 38 THEN 'CHGINDSTAT Incremental sysindexes stat changes'
WHEN 39 THEN 'CHGINDPG Direct change to sysindexes'
WHEN 40 THEN 'TXTPTR Info log row WHEN retrieving TEXTPTR'
WHEN 41 THEN 'TEXTINFO Info log for WRITETEXT/UPDATETEXT'
WHEN 42 THEN 'RESETIDENT Used WHEN a truncate table resets an identity value'
WHEN 43 THEN 'UNDO Compensating log record for Insert Only Row Locking (IORL)'
WHEN 44 THEN 'INSERT_IORL Insert with Row Locking record'
WHEN 45 THEN 'INSIND_IORL INSIND with IORL'
WHEN 46 THEN 'IINSERT_IORL IINDEX with IORL'
WHEN 47 THEN 'SPLIT_IORL Page split with IORL'
WHEN 48 THEN 'SALLOC_IORL Alloc new page for split with IORL'
WHEN 49 THEN 'ALLOC_IORL Allocation with IORL'
WHEN 50 THEN 'PREALLOCLOG Pre-allocate log space for CLRs'
ELSE 'Unknown Type'
END AS LOG_RECORD
FROM syslogs


For SQL Server 7 and above use DBCC LOG, this is also available in 6.5

DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}])

where:

dbid or dbname - Enter either the dbid or the name of the database

type - is the type of output, and includes these options:

0 - minimum information (operation, context, transaction id)

1 - more information (plus flags, tags, row length, description)

2 - very detailed information (plus object name, index name, page id, slot id)

3 - full information about each operation

4 - full information about each operation plus hexadecimal dump of the current transaction log's row.

-1 - full information about each operation plus hexadecimal dump of the current transaction log's row, plus Checkpoint Begin, DB Version, Max XDESID

by default, type = 0

To view the transaction log for the master database, run the following command:

DBCC log (master)