Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2001
    Location
    chennai
    Posts
    21

    Unanswered: Reading Transaction Log

    Hii all
    It has been for a requirement for a long time to read the transaction

    log of MS SQL Server. The Need for this is to write a audit trail

    program for our application.Also to See the Security breaches in the

    database.

    There is this tool from
    www.lumigent.com called Log Explorer. Its a fantastic tool to

    read the Log and the Only Known Tool. with it comes a few DLL's one of

    which is xp_logattach.dll , this has various extended stored procedures

    to attach the log,set the port,etc.....

    it will be nice if someone knows of a programming interface like SQL

    DMO to access the log. if this is found or developed somewhere it will

    help many DBA's and Application developers to have a fool proof way of

    managing changes in the database.

    if there are any microsoft developers in the SQL team. please get back

    to me on this query.

    Awaiting reply from the Best in the world

    Ramakrishnan.k
    Chennai
    K.Ramakrishnan
    Chennai

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •