MSSQLCity.Com - All about MS SQL
     
About Us  
SSWUG Articles  
Articles  
Administering  
Comparison  
General  
Know How  
Replication  
Tuning  
Undocumented  
UDF  
SQL 6.5  
FAQ  
Scripts  
Tips  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 

View Transaction Log

Alexander Chigrik
chigrik@mssqlcity.com


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 cannot modify syslogs system table manually.

This is from SQL Server Books Online (the syslogs table's columns):

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

There is no syslogs system table in SQL Server 7.0/2000. 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 use the following command:

DBCC log (master)


 

 
Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2010 Bits on the Wire, Inc