• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1563
  • 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?
0
lexo
Asked:
lexo
  • 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}] )

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)
0
 
lexoAuthor Commented:
Is there anyway to filter the results? (WHERE, etc.)
0
 
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.
http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-27-fn_dblog.htm
(see below for a transcript)

Jay  

---- 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
GO

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

SELECT * FROM ::fn_dblog(null, null)
GO
\-------- 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.  

0
 
ShogunWadeCommented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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