• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1543
  • Last Modified:

View Transaction Log

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?
  • 2
1 Solution
Jay ToopsCommented:
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)
lexoAuthor Commented:
Is there anyway to filter the results? (WHERE, etc.)
Jay ToopsCommented:

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.  

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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now