View Transaction Log

Posted on 2004-08-31
Last Modified: 2007-12-19
I've had peopletell me there isno way to view the transaction log.  However, I have found a company who offers software which does.  It's a bit pricey.  Is there a cheaper solution to viewing the SQL transaciton log?
Question by:lexo
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 10

Accepted Solution

Jay Toops earned 125 total points
ID: 11943139
You can use the following undocumented command in this case:

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 use the following command:

DBCC log (master)

Author Comment

ID: 11943301
Is there anyway to filter the results? (WHERE, etc.)
LVL 10

Expert Comment

by:Jay Toops
ID: 11943777

Then try this (in sql server 2000 )

SELECT * FROM ::fn_dblog(null,null)

and what it all means is a VERY tightly controlled ms secret
This is all I was able to find and it is as good as it gets.
(see below for a transcript)


---- transcript of URL FOLLOWS ----

fn_dblog returns a table of records from the transaction log.
The syntax of the call is:

    ::fn_dblog(@StartingLSN, @EndingLSN)
@StartingLSN and @EndingLSN are the start and ending
Log Sequence Numbers, also known as an LSN.  A NULL argument for
the Starting LSN requests log records from the beginning of the
transaction log. A NULL value for the ending LSN requests
information to the end of the transaction log.  

To get an idea of what goes into the database log, I backed up
my Pubs database to clear out the log.  Actually there were a few
records left in, I suppose from open transactions.  Then I ran
a simple update statement that changed one field in one row of
the Authors table.  Then I ran fn_dblog with NULL arguments
to see the entire log.  The script and it's results follow:

/------ Start copying below this line --------------------------\
-- Before running this script backup Pubs
-- Use the Pubs sample database.
USE pubs

-- make a minor change to the database
UPDATE Authors
    SET phone = '978-555-1212' -- New Information line!
    WHERE au_id = '238-95-7766'

SELECT * FROM ::fn_dblog(null, null)
\-------- Stop copying from above this line --------------------/
(Results - 1st group of columns)
Current LSN            Operation              Context            
---------------------- ---------------------- -------------------
0000001b:000001aa:0001 LOP_BEGIN_XACT         LCX_NULL          
0000001b:000001aa:0002 LOP_BEGIN_CKPT         LCX_NULL          
0000001b:000001ab:0001 LOP_XACT_CKPT          LCX_NULL          
0000001b:000001ab:0002 LOP_END_CKPT           LCX_NULL          
0000001b:000001ac:0001 LOP_MODIFY_ROW         LCX_BOOT_PAGE_CKPT
0000001b:000001ac:0002 LOP_MODIFY_ROW         LCX_BOOT_PAGE_CKPT
0000001b:000001ac:0003 LOP_FILE_HDR_MODIFY    LCX_FILE_HEADER    
0000001b:000001ae:0001 LOP_COMMIT_XACT        LCX_NULL          
0000001b:000001af:0001 LOP_BEGIN_XACT         LCX_NULL          
0000001b:000001af:0002 LOP_SET_BITS           LCX_DIFF_MAP      
0000001b:000001af:0003 LOP_MODIFY_ROW         LCX_CLUSTERED      
0000001b:000001af:0004 LOP_COMMIT_XACT        LCX_NULL      

(Results - 2nd Group of columns)
Transaction ID Tag Bits Log Record Length Previous LSN          
-------------- -------- ----------------- ----------------------
0000:00000b4e  0x0000                  84 00000000:00000000:0000
0000:00000000  0x0000                  96 0000001b:000001a5:0002
0000:00000000  0x0000                  88 00000000:00000000:0000
0000:00000000  0x0000                 136 0000001b:000001aa:0002
0000:00000000  0x0000                  84 00000000:00000000:0000
0000:00000000  0x0000                  92 00000000:00000000:0000
0000:00000000  0x0000                 448 00000000:00000000:0000
0000:00000b4e  0x0000                  52 0000001b:000001aa:0001
0000:00000b4f  0x0000                  60 00000000:00000000:0000
0000:00000000  0x0000                  56 00000000:00000000:0000
0000:00000b4f  0x0000                 104 0000001b:000001af:0001
0000:00000b4f  0x0000                  52 0000001b:000001af:0001
(Results - 3rd Group of Columns with some headers abridged)
 Flag... Object Name              Index... Page ID       Slot ID
 ------- ------------------------ -------- ------------- -------
 0x0200  NULL                     NULL     NULL          NULL    
 0x0000  NULL                     NULL     NULL          NULL    
 0x0000  NULL                     NULL     NULL          NULL    
 0x0000  NULL                     NULL     NULL          NULL    
 0x0000  dbo.ALLOCATION (99)      (0)      0001:00000009       0
 0x0000  dbo.ALLOCATION (99)      (0)      0001:00000009       0
 0x0000  dbo.ALLOCATION (99)      (0)      0001:00000000       0
 0x0200  NULL                     NULL     NULL          NULL    
 0x0200  NULL                     NULL     NULL          NULL    
 0x0000  dbo.ALLOCATION (99)      (0)      0001:00000006       1
 0x0200  dbo.authors (1977058079) (0)      0001:0000007f       2
 0x0200  NULL                     NULL     NULL          NULL    

Additional columns have been left out of this newsletter.  The
entire output of the query is in the file Vol1Num27_fn_dblog.txt
that you can find in the web version of this newsletter. It
includes all columns and rows shown above.

There's no documentation of the format of a log record in the
Books-OnLine (BOL) and I haven't been able to locate it anywhere
else.  However, there are a few obvious items of information in
the log.  LOP_BEGIN_XACT and LOP_COMMIT_XACT mark the beginning
and ending of the implicit transaction that surrounds the
UPDATE statement. The LOP_MODIFY_ROW operation on the object
dbo.Authors is an update to a single row.  Beyond that, you're
pretty much on your own.  

LVL 18

Expert Comment

ID: 11943821
agree with jl,   they are pretty much the only tools to do it that are built in.    But why do you need to view the transaction log anyway?

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 37
MS SQL Sever Import/export problem 7 52
Parsing this XML works but the other one doesn't 9 30
Applying Roles in Common Scenarios 3 22
I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question