SQL Server Performance Forum – Threads Archive

How to read transaction log completely

how should I read the transaction log files.?any stored procedure exists which can be helpful to read the transaction log
or any UI which can be used to read the ldf files

To read transaction log files, you need a 3rd party tool.
See ours sponsors.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Any free tools available for that or any other method.

DBCC LOG ( db_name ), but I doubt that you’ll find that terribly useful [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] – Frank http://www.insidesql.de</a>

Its showing something like this
this will not serve the purpose ,i need to see all the transactions that happned to the server /database till nw
is this possible any how
any tools ..any scripts..any thing 00000019:0000019f:0001LOP_BEGIN_CKPT LCX_NULL 0000:00000000
00000019:000001a0:0001LOP_END_CKPT LCX_NULL 0000:00000000

Didn’t I say, you won’t find it terribly useful? [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Luis already gave you the answer to your question. You need to *buy* a third-party tool. TANSTAAFL – Frank http://www.insidesql.de</a>

[email protected] TANSTAAFL!!! Microsoft gave me a free lunch once! But I did have to sit through some tedious sales pitches for software we’d already bought. Some kind of database software or other, I can’t quite recall the details. Tom Pullen
DBA, Oxfam GB

I’ve heard that SQL 2005 will be included with this facility to read the transaction log. Aaron, BTW what is the reason behind to read the transaction log and unless you use third party tools it is not an easy task. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Forum got some interestin topics also <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />)

I think part of it you can decode like your data, page no which was modified , record size . I will just show a small thing here — create a database
USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = ‘Page’)
DROP DATABASE Page;
GO
CREATE DATABASE Page
ON PRIMARY
( NAME=’Page_Data’,
FILENAME = ‘D:WorkPOCDatabaseEngineDataPage_Data.mdf’,
SIZE=3MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
LOG ON
( NAME=’Page_Log’,
FILENAME = ‘D:WorkPOCDatabaseEngineDataPage_Log.ldf’,
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);
GO
— create table test
Use Page
GO
CREATE TABLE Test (c1 int,c2 varchar(900))
CREATE CLUSTERED INDEX idx_Test ON Test (c1); — insert data into test
INSERT INTO Test VALUES (1, REPLICATE(‘a’,900)); — check the page allocation using DBCC IND. I can see page 80 as data page and 89 as IAM
DBCC IND (Page,Test,1); — DBCC PAGE on data page
DBCC TRACEON (3604)
DBCC PAGE (Page, 1, 80, 0) PAGE HEADER:
Page @0x0000000083BAE000 m_pageId = (1:80) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0xc000
m_objId (AllocUnitId.idObj) = 30 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039894016
Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 8 m_slotCnt = 1 m_freeCnt = 7177
m_freeData = 1013 m_reservedCnt = 0 m_lsn = (30:83:19)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
— Look at m_lsn 30:83:19. Converting to hex 30:83:19 = 1e:53:13 — fire a DBCC LOG command and look for this record
DBCC LOG (Page, 4) — Output — 0000001e:00000053:0013 LOP_INSERT_ROWS LCX_CLUSTERED 0000:000002c7 0 0x0000 62 1020 0000001e:00000053:0001 0x0002 74
0x00003E001E0000005300000001000200C70200000000020250000000010000001E0000001E000000530000001000000100000E000000000100000000000003009
[color=2]50[/color]300001A000313300008000100000003000002001100[color=3]9503[/color]
[color=5]61616161616161616161616161616161616161616161616161616161616161616161[/color]
0001000101000C0000E7A4787D0000010200040204000A0200C411BA730000 — In this text I have highlighted in bold 3 fields first is 50 (hex) = 80 (decimal) is the page no
second is 9503 (hex – reversed bytes) = 5930 = 0395 = 917 (decimal) is the record size
third is a strig of 61 = which is nothing but the column data ‘a’ I am still digging into it. If anyone has already please publish..
Thanks
j

Ahem, you have realised that you’ve replied to a 5 year old thread, haven’t you? [

]

Same goes for your other answer here: http://sql-server-performance.com/Community/forums/t/14483.aspx
[

]

I hope the content though is still applicaple as on date [

]

]]>