Solved

View Transaction Log

Posted on 2004-08-31
4
1,503 Views
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?
0
Comment
Question by:lexo
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
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}] )

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
 

Author Comment

by:lexo
ID: 11943301
Is there anyway to filter the results? (WHERE, etc.)
0
 
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.
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
 
LVL 18

Expert Comment

by:ShogunWade
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?
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now