A. A. Most of the information in the SQL log (syslogs) is not accessible via standard SQL commands. There are ways of accessing this information from SQL 6.5 and below. Ostensibly because syslogs is held (more or less) as a SQL table. With SQL 7 this is no longer the case, and there is no api/fil format published for it. Therefore log viewing tools for 7.0 are unlikely to appear any time soon.
SQL 6.5 and below options are :-
1. You can get transaction id and operation type only (no data) through a select * from syslogs. Or use the following code (courtesy of Tibor Karaszi) to make it a bit more readable.
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
2. dbcc log command. Not well documented, but some details below for SQL 6.5.Note that as with most undocumented dbcc commands you need to do a dbcc traceon(3604) first to see the output.
3. 3rd party. Logview from www.dbsg.com.
4. 3rd party. Image Analyzer from www.platinum.com. This product also allows extracting data and SQL statements from the log.
---------------------------------------
dbcc log \[ (@dbid, @objid, @pagenum, @rownum, @records, @type \[, @printopt\]) \]
dbcc log (5, 0, 0, 0, -1, 0, 1) // Show the last begin transaction record in the log
Parameters:
@dbid Database ID
@objid Object ID
A negative value indicates that @pagenum & @rownum represent a row in the log to use as a starting point in the scan of the log.
A value of zero indicates that log records for changes to @pagenum will be included in the commands output.
A positive value followed by a non-zero value for @pagenum indicates that @pagenum and @rownum represent a transaction ID. Log records for that transaction will be included in the output.
A positive value followed by zero values for @pagenum and @rownum indicates an object ID. Log records for changes to that object will be included in the output.
@pagenum page number
@rownum row number in the log
Together with @pagenum, this is either a starting point in a scan of the log or a transaction id.
@records number of records to examine. If positive, the first
@type
@printopt
