Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 27, 2000

Viewing the Transaction Log

By Alexander Chigrik


Introduction
SQL Server 6.5
SQL Server 7.0
Literature

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

Every database contains the syslogs system table in SQL Server 6.5. This table contains the transaction log, and is used by SQL Server for recovery and roll forward. You can not modify syslogs system table manually.

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

Column  Datatype        Description

xactid  binary(6)       Transaction ID
op      tinyint         Update-operation number
So, to view the transaction log you can use the following select statement:
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

There is no syslogs system table in SQL Server 7.0. The database log is now an operating system file. So this SQL statement:

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
To view the transaction log for the master database, you can run the following command:

DBCC log (master)


Literature

1. SQL Server Books Online.

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




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM