SQL Server Performance

How to read transaction log completely

Discussion in 'General DBA Questions' started by aaronsandy, Feb 10, 2005.

  1. aaronsandy New Member

    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
  2. Luis Martin Moderator

    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.



  3. aaronsandy New Member

    Any free tools available for that or any other method.
  4. FrankKalis Moderator

    DBCC LOG ( db_name ), but I doubt that you'll find that terribly useful [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  5. aaronsandy New Member


    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
  6. FrankKalis Moderator

    Didn't I say, you won't find it terribly useful? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />Luis already gave you the answer to your question. You need to *buy* a third-party tool. TANSTAAFL<br /><br />--<br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  7. thomas New Member

    LOL@ 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
  8. satya Moderator

    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.
  9. gkrishn New Member

    Forum got some interestin topics also <img src='/community/emoticons/emotion-1.gif' alt=':)' />)
  10. joshhhhhh New Member

    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

  11. FrankKalis Moderator

    Ahem, you have realised that you've replied to a 5 year old thread, haven't you? [;)]
  12. FrankKalis Moderator

    Same goes for your other answer here: http://sql-server-performance.com/Community/forums/t/14483.aspx
    [:)]
  13. joshhhhhh New Member

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

Share This Page