lexo
asked on
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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?
ASKER