Viewing Transaction Logs


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Viewing Transaction Logs

  1. #1
    Kristin Piskulic Guest

    Viewing Transaction Logs


    Is there anyway to view a transaction log? I'm trying to find out what was
    happening on a given database at a given time and thought the transaction
    log that I had stored off for that day would be my best bet.

    Thanks,
    Kristin Piskulic

  2. #2
    Michael Levy Guest

    Re: Viewing Transaction Logs

    There is a company that make a product for viewing a 6.x transaction log.
    Sorry I don't remember the name.

    -Mike
    --
    Michael Levy MCSD, MCDBA, MCT
    Consultant
    GA Sullivan
    michaell@gasullivan.com

    "Kristin Piskulic" <kpiskulic@microcon.com> wrote in message
    news:390d8694$1@news.devx.com...
    >
    > Is there anyway to view a transaction log? I'm trying to find out what

    was
    > happening on a given database at a given time and thought the transaction
    > log that I had stored off for that day would be my best bet.
    >
    > Thanks,
    > Kristin Piskulic




  3. #3
    Neil Pike Guest

    Re: Viewing Transaction Logs

    Kristin.

    > Is there anyway to view a transaction log? I'm trying to find out what was
    > happening on a given database at a given time and thought the transaction
    > log that I had stored off for that day would be my best bet.


    Q. How can I view the SQL Server log? Can I recover individual
    transactions/data from it?

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

    ===

    v1.00 1999.09.22
    Applies to SQL Server versions : All
    FAQ Categories : Database Administration, Troubleshooting
    Related FAQ articles : n/a
    Related Microsoft Kb articles : n/a
    Other related information : n/a
    Authors : Neil Pike, Tibor Karaszi
    Neil Pike MVP/MCSE. Protech Computing Ltd
    (Please reply only to newsgroups)
    SQL FAQ (428 entries) see
    forumsb.compuserve.com/vlforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7
    - SQL Public)
    or www.ntfaq.com/sql.html
    or www.sql-server.co.uk
    or www.mssqlserver.com/faq



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center