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.
This is from SQL Server Books Online (fields of syslogs table):
There are some op values:
will return error:
You can use the following undocumented command in this case:
PARAMETERS:
2. "How can I view the SQL Server log?"
3. "Update Methods Used in MS SQL 6.5"
4. "Update Methods Used in MS SQL 7.0"
Introduction
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.
So, to view the transaction log you can use the following select statement:
Column Datatype Description
xactid binary(6) Transaction ID
op tinyint Update-operation number
SELECT xactid AS TRAN_ID, op AS LOG_RECORD FROM syslogs
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
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'syslogs'.
DBCC log ( {dbid|dbname}, [, type={0|1|2|3|4}] )
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.
http://www.ntfaq.com/Faq.cfm?FaqID=686
http://mobile.databasejournal.com/features/mssql/article.php/1442311
http://mobile.databasejournal.com/features/mssql/article.php/1443671
»
See All Articles by Columnist Alexander Chigrik
In this article Alexander Chigrik shows how you can view the SQL Server transaction log for version 6.5 and for version 7.0.