Viewing the Transaction Log

Introduction

In this article I want to show how you can view the SQL Server transaction log for version 6.5 and for version 7.0.

It's not necessary to view the SQL Server transaction log, but if you are a DBA, it may be useful to resolve some problems. It may be useful for understanding the internal SQL Server architecture too.

SQL Server 6.5

This is from SQL Server Books Online (fields of syslogs table):

Column  Datatype        Description

xactid  binary(6)       Transaction ID
op      tinyint         Update-operation number

SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs

There are some op values:

op = 0  - BEGIN TRANSACTION
op = 4  - Insert Row
op = 5  - Delete Row
op = 9  - Modify Row
op = 30 - END TRANSACTION

SQL Server 7.0

SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs

will return error:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'syslogs'.

You can use the following undocumented command in this case:

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

PARAMETERS:

   Dbid or dbname - Enter either the dbid or the name of the database
                    in question.

      type - is the type of output:

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

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

      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.

by default type = 0

DBCC log (master)

Literature

2. "How can I view the SQL Server log?"
http://www.ntfaq.com/Faq.cfm?FaqID=686

3. "Update Methods Used in MS SQL 6.5"
http://www.databasejournal.com/features/mssql/article.php/1442311

4. "Update Methods Used in MS SQL 7.0"
http://www.databasejournal.com/features/mssql/article.php/1443671

» See All Articles by Columnist Alexander Chigrik

Tools:

Add databasejournal.com to your favorites Add databasejournal.com to your browser search box IE 7 | Firefox 2.0 | Firefox 1.5.x Receive news via our XML/RSS feed