locked
What the point of storing log if log is inaccurate?

    Question

  • By making library that works with log (SQL Server 2014 developer edition, FULL recovery mode) I found that frequently log is inaccurate. For example I compared result of fn_dblog with data stored in "ldf" and sometime I see proper LOP_COMMIT_XACT in fn_dblog while ldf contains garbage. Or data in ldf broken by 0x40 formatting every 0x200 bytes like VARIANT field VarData started exactly at 0x457E00 and byte with TDS type replaced by 0x40

    457E00 40 01 40 1F 3E 91 02 00 A3 E1 3F 3F 3F 3F A1 4B   |  @.@.>.....????.K
    457E10 3F 3F 3F 3F 53 3F 3F 3F 5A 3F 3F A1 A5 A1 A6 A1   |  ????S???Z??.....

    BLOBs a complete nightmare due to that formatting like'...0123456789abcdef0123456789abcdef0123@5678...'

    So what the point of log file if it is so inaccurate? Is there way to tell server to be more accurate?

    Wednesday, July 04, 2018 12:19 PM

All replies

  • The transaction log contains all sorts of information. It contains committed and uncommitted transactions as well as other tracking information - for example for replication and CDC.

    It provides an accurate record for recovery operations, but may not provide you with the an accurate record for the information you are trying to retrieve from it. Perhaps extended events might be a better way to persisting the information you are looking for.

    What exactly are you trying to achieve?

    Wednesday, July 04, 2018 12:28 PM
  • The problem is: it provides accurate record frequently, but not always. Like 95% accurate. Sometime there wrong alloc units in log record while related page contains another, sometime garbage instead of proper data (I stored copy of fn_dblog and compared that with information stored in mdf). For example one ldf file contains at LSN 22:19C:03 (LOP_COMMIT_XACT) garbage

    01 00 00 00 35 36 37 38   |  ............5678
    35950 39 30 39 38 37 36 35 66 65 64 63 62 61 00 00 00   |  9098765fedcba...
    35960 13 3B 6B 9C 58 00 00 00 00 1B 05 7C 02 00 00 00   |  .;k.X......|....

    instead of data it had in fn_dblog (stored into separate text file before stopping SQL Server service to copy files)

    0x00005000220000009C0100000100020056030000000081001AC0CE0006A90000220000009C010000010000000000000000000000000000000000000000000000C4AE040000000000000000000000000000000000

    and blobs full of 0x40 every 0x200 bytes.

    Should I upload files to prove it? I'm trying to get transactions from ldf log file.

    P.S.: I found where proper info for LOP_COMMIT_XACT, it exists but offset at the end of block is wrong. Record

    00 00 50 00 22 00 00 00   |  ..........P."...
    359B0 9C 01 00 00 01 00 02 00 56 03 00 00 00 00 81 00   |  ........V.......
    359C0 1A C0 CE 00 06 A9 00 00 22 00 00 00 9C 01 00 00   |  ........".......

    at offset 0x1A8 while offset in block points to 0x148

    359F0 00 00 00 00 00 00 00 00 00 00 00 00 05 00 02 00   |  ................
    35A00 48 01 98 00 30 00 01 05 24 01 00 00 01 00 FF FF   |  H...0...$.......

    • Edited by s73 Wednesday, July 04, 2018 2:52 PM
    Wednesday, July 04, 2018 2:23 PM
  • The problem is: it provides accurate record frequently, but not always. Like 95% accurate. Sometime there wrong alloc units in log record while related page contains another, sometime garbage instead of proper data (I stored copy of fn_dblog and compared that with information stored in mdf). For example one ldf file contains at LSN 22:19C:03 (LOP_COMMIT_XACT) garbage

    Not sure if I understand your question correctly but the mismatch of LSN between the Log record and the LSN in the corresponding data page header is due to the fact that each log record keeps track of two LSNs whereas, a data page header contains only one LSN.

    Of the two LSNs in a log record, one LSN is the actual log record's LSN and the other LSN is the one that existed on the corresponding data page before the change was recorded in the log. So, during recovery, when the log records are redone, each log record's LSN is compared with the LSN of the corresponding data page. if the data page LSN is equal to the previous LSN of the log record, the change indicated in the log record is redone or else, if the data page LSN is equal to or higher than the "Actual LSN" of the log record, then the redo is skipped. 

    As for your question, it could be that the log record was already written to the log file but the corresponding data page was still in the buffer cache and the one you read would have been the previous version from the MDF and hence the mismatch. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.



    Thursday, July 05, 2018 12:39 AM
  • I believe nothing is garbage in LDF file.

    You need some expertise to read fn_dblog dumps.

    Do you reading [Log Record] column from fn_dblog?

    For example: 
    1) When you insert a row, fn_dblog dump row data in [RowLog Contents 0] as LOP_INSERT_ROWS operation.
    2) When you update a row, fn_dblog dumps modified bytes on [RowLog Contents 1] as LOP_MODIFY_ROW operation.
    3) When you delete a row, fn_dblog dumps row data in [RowLog Contents 0] as LOP_DELETE_ROWS operation.

    What you need to achieve exactly?

    I'm developing an Transaction Log Audit and Recovery Tool currently.
    A bit of reverse engineering and study is necessary to understand transaction log dump/structure.

    Thursday, July 05, 2018 2:18 AM

  • In some cases mismatch of AllocUnitId between information stored in ldf and AllocUnitId of page in mdf. For example in ldf for query

    CREATE TABLE [dbo].[VAR_TYPES] (
      uid INT IDENTITY,
      c_decimal [decimal](18, 2) NULL,
      c_numeric [numeric](18, 4) SPARSE,
      c_money [money] NULL,
      c_smallmoney [smallmoney] SPARSE,
      c_float [float] NULL ,
      c_real [real] SPARSE,
      c_varchar10 [varchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,
      c_varchar [varchar] (260) COLLATE Cyrillic_General_CI_AS SPARSE,
      c_nvarchar10 [nvarchar] (10) COLLATE Cyrillic_General_CI_AS NULL ,
      c_nvarchar [nvarchar] (260) COLLATE Cyrillic_General_CI_AS SPARSE,
      c_sql_variant [sql_variant] NULL ,
      c_datetime2 DATETIME2 NULL,
      c_datetimeoffset DATETIMEOFFSET SPARSE,
      c_timestamp TIMESTAMP NULL,
      CONSTRAINT PK_VAR_TYPES PRIMARY KEY (uid)
    ) ON [PRIMARY]
    GO
    
    INSERT INTO VAR_TYPES(c_decimal, c_numeric, c_money, c_smallmoney, c_float, c_real, c_varchar10, c_varchar, c_nvarchar10, c_nvarchar, c_sql_variant, c_datetime2, c_datetimeoffset) 
    VALUES(1234.56, 1234.5678, 1234.56, 1234.56, 1234.5678, 1234.5678, 'test', 'test', 'test', 'test', 'variant', '2017-12-18 15:18:35', '2017-12-18 15:18:35')
    GO
    
    INSERT INTO VAR_TYPES(c_decimal, c_numeric, c_money, c_smallmoney, c_float, c_real, c_varchar10, c_varchar, c_nvarchar10, c_nvarchar, c_sql_variant, c_datetime2, c_datetimeoffset) 
    VALUES(NULL, 4.5678, 4.56, 4.56, 4.5678, 4.5678, NULL, 'test2', 'test2', 'test2', 1234.56, '2017-12-18 15:26:35', '2017-12-18 15:26:35')
    GO
    

    first insert stored (not always, it can happen) with AllocUnitId 256:72:0 (72057594042646528) that leads to "sys.queue_messages_1977058079.queue_clustered_index" AllocUnit instead of proper 256:120:0 (72057594045792256) of "dbo.VAR_TYPES.PK_VAR_TYPES".

    https://drive.google.com/open?id=1Q7bpCF81UAGb3S9SVEDCPhzBDxU8EpN0 - ldf, mdf and sql zipped. You can check AllocUnit at LSN 00000021:00000101:0019.

    Thursday, July 05, 2018 9:06 AM
  • fn_dblog() reading from log cache. After CHECKPOINT or service restart it will be empty with new information. So I'm reading from ldf file.

    • Edited by s73 Thursday, July 05, 2018 12:48 PM
    Thursday, July 05, 2018 9:11 AM
  • Fn_dblog() will read all VLFs (inactive or active) from transaction log when enabling trace flag 2537.

    I've identified LSN 00000021:00000101:0019 reference at offset 0x00022d0c, some references to AllocUnitId 72057594045792256, but none reference to AllocUnitId 72057594042646528.

    Inspecting allocation unit 72057594042646528 in MDF data file and it is empty.

    "fn_dblog() reading from log cache. After CHECKPOINT or service restart it will be empty with new information."
    Only inactives VLFs can be overwritten, but service restart does not run ghost cleanup task.

    "and blobs full of 0x40 every 0x200 bytes."
    If you see some 0x40 and 0x80 i suppose this is related with Parity like DBCC LOGINFO shows (internal use).

    Have you surely understand transaction log file structure?
    Do you can upload some print screen as a proof this trouble?
    What you are trying to do?



    Friday, July 06, 2018 2:51 AM
  • Example with blobs and proper data in ldf replaced by 0x40 https://drive.google.com/open?id=1OjW4jvo6KNT_hohwfbh_RbePfAeDGzsU , for example LSN 00000022:00000131:0009 where in LOP_MODIFY_ROW "...30313233403536..." instead of "...30313233343536..."

    It is stored that way in ldf:

    287E0 34 35 36 37 38 39 61 62 63 64 65 66 30 31 32 33   |  456789abcdef0123
    287F0 34 35 36 37 38 39 61 62 63 64 65 66 30 31 32 33   |  456789abcdef0123
    28800 40 35 36 37 38 39 61 62 63 64 65 66 30 31 32 33   |  @56789abcdef0123
    28810 34 35 36 37 38 39 61 62 63 64 65 66 30 31 32 33   |  456789abcdef0123
    28820 34 35 36 37 38 39 61 62 63 64 65 66 30 31 32 33   |  456789abcdef0123

    Error with TDS type of variant type overwritten by 0x40 from first message in https://drive.google.com/open?id=1L1r2q4IKJxJevVQe0L4Ugaesz9p8wDOS at ldf file offset 457E00 LSN 00000035:00000100:007C.

    I'm trying to read LDF files and get transaction info from there. Did you actually tried to read LDF file or used DBCC? In case of wrong AllocUnitId I also can check for AllocUnitId at referenced page and fix it if possible. But I'm talking about properly storing at server side without workarounds.
    • Edited by s73 Friday, July 06, 2018 6:23 PM
    Friday, July 06, 2018 5:28 PM
  • It used to be that you could partner with Microsoft and they would give you details about transaction log and database internals. I am reaching out to a contact there for how to get registered in this program.
    Friday, July 06, 2018 5:46 PM
  • The transaction log is an internal structure MS uses to populate the data file.  It is not really for human or non-SQL Server service consumption.

    What exactly are you trying to accomplish with reading the ldf file?  Likely there is a better way to accomplish what you are trying to do.

    Friday, July 06, 2018 5:59 PM
    Moderator
  • I've identified LSN 00000021:00000101:0019 reference at offset 0x00022d0c, some references to AllocUnitId 72057594045792256, but none reference to AllocUnitId 72057594042646528.

    LSN 00000021:00000101:0019 in ldf file record header

    229E0 00 00 3E 00 21 00 00 00 01 01 00 00 04 00 02 00   |  ..>.!...........
    229F0 0E 03 00 00 00 00 02 02 4E 00 00 00 01 00 00 00   |  ........N.......
    22A00 48 00 00 00 21 00 00 00 01 01 00 00 15 00 00 01   |  H...!...........
    22A10 00 00 28 00 00 00 00 01 00 00 00 00 00 00 03 00   |  ..(.............

    where AllocUnitId 0x100:0x48:0 (72). While at referenced page 0x4E AllocUnitId.idObj 0x78 (120)

    9C000 01 01 00 00 00 82 00 01 00 00 00 00 00 00 31 00   |  ..............1.
    9C010 00 00 00 00 00 00 03 00 78 00 00 00 C0 1D 67 03   |  ........x.....g.
    9C020 4E 00 00 00 01 00 00 00 21 00 00 00 12 01 00 00   |  N.......!.......
    9C030 01 00 00 00 16 03 00 00 00 00 00 00 58 85 06 3D   |  ............X..=
    9C040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   |  ................
    9C050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   |  ................

    It is error in LDF file. In next LOP_INSERT_ROWS LSN 00000021:00000106:0003 server stored it properly

    00 00 3E 00 21 00 00 00   |  ..Y.P.E...>.!...
    22CF0 06 01 00 00 02 00 02 00 11 03 00 00 00 00 02 02   |  ................
    22D00 4E 00 00 00 01 00 01 00 78 00 00 00 21 00 00 00   |  N.......x...!...
    22D10 01 01 00 00 19 00 00 01 00 00 28 00 00 00 00 01   |  ..........(.....
    22D20 00 00 00 00 00 00 03 00 98 00 00 00 1A 00 00 00   |  ................

    Friday, July 06, 2018 6:09 PM
  • Example with blobs and proper data in ldf replaced by 0x40 https://drive.google.com/open?id=1OjW4jvo6KNT_hohwfbh_RbePfAeDGzsU , for example LSN 00000022:00000131:0009 where in LOP_MODIFY_ROW "...30313233403536..." instead of "...30313233343536..."

    It is stored that way in ldf:

    287E0 34 35 36 37 38 39 61 62 63 64 65 66 30 31 32 33   |  456789abcdef0123
    287F0 34 35 36 37 38 39 61 62 63 64 65 66 30 31 32 33   |  456789abcdef0123
    28800 40 35 36 37 38 39 61 62 63 64 65 66 30 31 32 33   |  @56789abcdef0123
    28810 34 35 36 37 38 39 61 62 63 64 65 66 30 31 32 33   |  456789abcdef0123
    28820 34 35 36 37 38 39 61 62 63 64 65 66 30 31 32 33   |  456789abcdef0123

    Error with TDS type of variant type overwritten by 0x40 from first message in https://drive.google.com/open?id=1L1r2q4IKJxJevVQe0L4Ugaesz9p8wDOS at ldf file offset 457E00 LSN 00000035:00000100:007C.

    I'm trying to read LDF files and get transaction info from there. Did you actually tried to read LDF file or used DBCC? In case of wrong AllocUnitId I also can check for AllocUnitId at referenced page and fix it if possible. But I'm talking about properly storing at server side without workarounds.

    About 0x40 in each 0x200 (512 bytes = 1 disk sector). It's seems to be an Log Parity Check like Data File TORN_PAGE_DETECTION.

    Please, read this post: https://www.sqlskills.com/blogs/paul/search-engine-qa-24-why-cant-the-transaction-log-use-instant-initialization/

    Firstly i did some reverse engineering on LDF file, but my tool need to use fn_dblog (need to audit with service online).
    Saturday, July 07, 2018 4:17 AM
  • It used to be that you could partner with Microsoft and they would give you details about transaction log and database internals. I am reaching out to a contact there for how to get registered in this program.
    That's interesting information.
    Saturday, July 07, 2018 4:23 AM
  • I've identified LSN 00000021:00000101:0019 reference at offset 0x00022d0c, some references to AllocUnitId 72057594045792256, but none reference to AllocUnitId 72057594042646528.

    LSN 00000021:00000101:0019 in ldf file record header

    229E0 00 00 3E 00 21 00 00 00 01 01 00 00 04 00 02 00   |  ..>.!...........
    229F0 0E 03 00 00 00 00 02 02 4E 00 00 00 01 00 00 00   |  ........N.......
    22A00 48 00 00 00 21 00 00 00 01 01 00 00 15 00 00 01   |  H...!...........
    22A10 00 00 28 00 00 00 00 01 00 00 00 00 00 00 03 00   |  ..(.............

    where AllocUnitId 0x100:0x48:0 (72). While at referenced page 0x4E AllocUnitId.idObj 0x78 (120)

    9C000 01 01 00 00 00 82 00 01 00 00 00 00 00 00 31 00   |  ..............1.
    9C010 00 00 00 00 00 00 03 00 78 00 00 00 C0 1D 67 03   |  ........x.....g.
    9C020 4E 00 00 00 01 00 00 00 21 00 00 00 12 01 00 00   |  N.......!.......
    9C030 01 00 00 00 16 03 00 00 00 00 00 00 58 85 06 3D   |  ............X..=
    9C040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   |  ................
    9C050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   |  ................

    It is error in LDF file. In next LOP_INSERT_ROWS LSN 00000021:00000106:0003 server stored it properly

    00 00 3E 00 21 00 00 00   |  ..Y.P.E...>.!...
    22CF0 06 01 00 00 02 00 02 00 11 03 00 00 00 00 02 02   |  ................
    22D00 4E 00 00 00 01 00 01 00 78 00 00 00 21 00 00 00   |  N.......x...!...
    22D10 01 01 00 00 19 00 00 01 00 00 28 00 00 00 00 01   |  ..........(.....
    22D20 00 00 00 00 00 00 03 00 98 00 00 00 1A 00 00 00   |  ................

    It's simple.

    Allocation Unit ID is an 8-byte number.

    In page header it's composed by m_objId (4-byte number at offset 0x18) and m_IndexId (2-byte number at offset 0x6).

    SQL Server keeps these 8-byte IDs in sys.sysallocunits internal table.

    In LDF file, the Allocation Unit ID appears as 8-byte number, so you need to consider bytes sequence: 00 00 78 00 00 00 00 01 (72057594045792256) and 00 00 48 00 00 00 00 01 (72057594042646528).

    The formula to calculate AllocUnitId based on m_objId and m_IndexId is described here: https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-how-are-allocation-unit-ids-calculated/

    But these values in page header is maintained for consistence purpose with sys.sysallocunits table.



    Saturday, July 07, 2018 5:16 AM
  • About 0x40 in each 0x200 (512 bytes = 1 disk sector). It's seems to be an Log Parity Check like Data File TORN_PAGE_DETECTION.

    Please, read this post: https://www.sqlskills.com/blogs/paul/search-engine-qa-24-why-cant-the-transaction-log-use-instant-initialization/

    Firstly i did some reverse engineering on LDF file, but my tool need to use fn_dblog (need to audit with service online).

    I think the problem is server writing those 0x40 (64) as mentioned by link "As the log is written, each 512-byte sector in a VLF has parity bits stamped on it (initially 64)" over data or just don't care about data will be broken. So this is inaccuracy as intended it seems. You asked for transaction log, we made it, here is your hundreds of megabytes. No one using it anyway.
    Wednesday, July 11, 2018 5:30 PM
  • It's simple.

    Allocation Unit ID is an 8-byte number.

    In page header it's composed by m_objId (4-byte number at offset 0x18) and m_IndexId (2-byte number at offset 0x6).

    SQL Server keeps these 8-byte IDs in sys.sysallocunits internal table.

    In LDF file, the Allocation Unit ID appears as 8-byte number, so you need to consider bytes sequence: 00 00 78 00 00 00 00 01 (72057594045792256) and 00 00 48 00 00 00 00 01 (72057594042646528).

    The formula to calculate AllocUnitId based on m_objId and m_IndexId is described here: https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-how-are-allocation-unit-ids-calculated/

    But these values in page header is maintained for consistence purpose with sys.sysallocunits table.

    I know how to get AllocUnitId from ldf and mdf. So far I am able to extract tables from mdf (up to page compression), log records from ldf like fn_dblog and transaction information up to sql queries executed. The problem is server inaccuracy during saving transaction log into ldf file. Imagine scenario when table created, something inserted, deleted, updated and then table dropped (info about object removed from system tables). There I should trace insertion into sys.syscolpars, but due to inaccuracy for example I have field name like 'c_datetim䁥offset' due to server put 0x40 right into field's name

    20770 00 00 3E 00 21 00 00 00 D4 00 00 00 49 00 02 00   |  ..>.!.......I...
    20780 05 03 00 00 00 00 02 02 3F 00 00 00 01 00 10 00   |  ........?.......
    20790 29 00 00 00 21 00 00 00 D4 00 00 00 48 00 01 00   |  )...!.......H...
    207A0 00 00 29 00 00 00 01 00 00 00 00 00 00 00 03 00   |  ..).............
    207B0 55 00 00 00 16 00 00 00 30 00 2D 00 E9 30 A3 0E   |  U.......0.-..0..
    207C0 00 00 0E 00 00 00 2B 2B 00 00 00 0A 00 22 07 00   |  ......++....."..
    207D0 00 00 00 00 00 00 00 0A 00 00 00 00 00 00 00 00   |  ................
    207E0 00 00 00 00 00 10 00 00 80 01 00 55 00 63 00 5F   |  ...........U.c._
    207F0 00 64 00 61 00 74 00 65 00 74 00 69 00 6D 00 65   |  .d.a.t.e.t.i.m.e
    20800 40 6F 00 66 00 66 00 73 00 65 00 74 00 00 00 00   |  @o.f.f.s.e.t....

    Probably while this data in server cache for rollback purposes it's intact, but in ldf log it is partially useful.

    About being partner with Microsoft: for what? To say "hey guys, your log contains lot of garbage"? To pay for bug reporting?


    • Edited by s73 Thursday, July 12, 2018 6:56 AM
    Thursday, July 12, 2018 6:54 AM
  • There's nothing inaccurate in the log file. Period. The problem is you're not reading it correctly. The log reader class which fn_dblog (that I partially wrote for SQL Server 2005) and other log scanners use, deals with all of the formatting within the log blocks and VLFs in the log file. None of that is documented, and the log internals document that Hilary mentioned wasn't updated after SQL Server 2005 - so there's no documented information on how to read and parse log file contents.

    If you would answer the multiple requests for an explanation of what you're trying to achieve, we could maybe suggest a solution for you. Continuing to assert that there's something wrong with the log file contents isn't productive, or correct in any way whatsoever.

    Thanks

    [Edit] PS There is no separate format for what's in the log file on disk vs. what's in the log cache; the log cache is just a small in-memory cache of log blocks that have recently been written to disk.


    CEO, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet and SQL Server Magazines, Author of 2005 DBCC CHECKDB/repair. Consulting/Training: http://www.sqlskills.com/


    Thursday, July 12, 2018 8:49 AM
    Moderator
  • There's nothing inaccurate in the log file. Period. The problem is you're not reading it correctly. The log reader class which fn_dblog (that I partially wrote for SQL Server 2005) and other log scanners use, deals with all of the formatting within the log blocks and VLFs in the log file. None of that is documented, and the log internals document that Hilary mentioned wasn't updated after SQL Server 2005 - so there's no documented information on how to read and parse log file contents.

    If you would answer the multiple requests for an explanation of what you're trying to achieve, we could maybe suggest a solution for you. Continuing to assert that there's something wrong with the log file contents isn't productive, or correct in any way whatsoever.

    Thanks

    [Edit] PS There is no separate format for what's in the log file on disk vs. what's in the log cache; the log cache is just a small in-memory cache of log blocks that have recently been written to disk.


    CEO, SQLskills.com (http://www.sqlskills.com/blogs/paul), SQL MVP, Microsoft Regional Director, Contributing Editor of TechNet and SQL Server Magazines, Author of 2005 DBCC CHECKDB/repair. Consulting/Training: http://www.sqlskills.com/


    I answered several times "I'm trying to read LDF files and get transaction info from there". Now you know. Is that changed anything?

    Your information always incorrect or useless, I don't know who always puts 3 likes to your messages (liking yourself?).

    1. LDF log structure up to SQL Server 2012+ explained in "SQL Server internals". Sources also available in projects like OrcaMdf (works with both mdf and ldf).
    2. You not finished your projects even for SQL Server 2005 and claiming your know and understand everything. And even if you will finish making your project information will be outdated due to internals changed twice since then in 2008 and 2012.


    • Edited by s73 Thursday, July 12, 2018 3:06 PM
    Thursday, July 12, 2018 2:46 PM
  • Ok, you are trying to "read the LDF file and get transaction info".  What exactly are you trying to do with that information? 

    You are likely better off using something else than trying to parse an internal file structure.  There are already tools which get data about transactions, like CDC, replication, etc.

    Thursday, July 12, 2018 2:55 PM
    Moderator
  • Whoa! S73...These guys are just trying to help by giving YOU their precious time and they're being nice about it. Most of them get paid hourly for what they do. 

    We can obviously see that you're trying to read the LDF files because you stated you were using fn_dblog. which also indicates you are looking at transaction info from there. 

    You are apparently doing something that the log file was not designed for which is why each and every person has asked, what is your purpose for needing the transactions from the log file. What do you intend to do with them. There are definitely better solutions for what you are trying to do. 

    These guys are some of the best help you can get when talking about SQL Server. 

    s73, If you're not going to be behave, and be respectful please stop responding. 


    Mike Lawell MCSA: SQL Server 2012 http://www.sqlserverassociates.com Twitter: @sqldiver LinkedIn: https://www.linkedin.com/in/mikelawell

    Thursday, July 12, 2018 4:32 PM
  • Whoa! S73...These guys are just trying to help by giving YOU their precious time and they're being nice about it. Most of them get paid hourly for what they do. 

    We can obviously see that you're trying to read the LDF files because you stated you were using fn_dblog. which also indicates you are looking at transaction info from there. 

    You are apparently doing something that the log file was not designed for which is why each and every person has asked, what is your purpose for needing the transactions from the log file. What do you intend to do with them. There are definitely better solutions for what you are trying to do. 

    These guys are some of the best help you can get when talking about SQL Server. 

    s73, If you're not going to be behave, and be respectful please stop responding. 


    Mike Lawell MCSA: SQL Server 2012 http://www.sqlserverassociates.com Twitter: @sqldiver LinkedIn: https://www.linkedin.com/in/mikelawell

    He said "There's nothing inaccurate in the log file. Period.". Like: stop talking, you are wrong, there is nothing to discuss.

    I started from retrieving the same result from ldf like fn_dblog do. So I saved every fn_dblog. These are steps from knowing nothing to reproducing results as SQL from transaction log (almost year spent). I checked that my list of LSN are the same, allocation units extracted and are the same, etc. Every error was checked and doublechecked up to exact bytes in files. And every error in ldf requires extra code and try/catch to resolve mess if it happens. After year and 10+ created and analyzed (literally analyzed by records and bytes) databases with all possible types, sparse, compressions, constraints etc I got collection of bugs in ldf files. So I'm sure these are bugs, not just some malfunction. But what the point of having 100 MB log file while you can't be sure that information there is correct?

    First I was expecting something like "It is known bug of SQL Server 2014". But no one knows, no one cares, no one checks.

    I am sure that what I doing it is exactly what transaction log designed for: information about transactions so I could be able to extract what happened. And it works, but those bugs make it unreliable. If there symbol in field name is broken - what can I do? Nothing. If object still exists in system table I can extract CREATE SQL for table from there, if table dropped I must rely on information from log where LOP_INSERT_ROWS into sys.syscolpars contains

    id = 245575913;
    number = 0;
    colid = 14;
    name = 'c_datetim䁥offset';
    xtype = 43;
    utype = 43;
    length = 10;
    prec = 34;
    scale = 7;
    collationid = 0;
    status = 0;
    maxinrow = 10;
    xmlns = 0;
    dflt = 0;
    chk = 0;
    idtval = NULL;

    due to 00 6F replaced by 40 6F and character "e" is broken. And broken symbol even not worst case scenario. When TDS type broken in Variant field I cannot extract value at all.


    • Edited by s73 Thursday, July 12, 2018 5:12 PM typo
    Thursday, July 12, 2018 5:09 PM
  • "Your information always incorrect or useless, I don't know who always puts 3 likes to your messages (liking yourself?)."
    He does not respond clearly because does not have the information or can not answer (NDA).

    "s73, If you're not going to be behave, and be respectful please stop responding. "
    No one gets things on slovenliness.
    If you suspect bugs, report it to Microsoft.

    "due to 00 6F replaced by 40 6F and character "e" is broken. And broken symbol even not worst case scenario. When TDS type broken in Variant field I cannot extract value at all."
    These bytes replaced by parity bits (0x40 or 0x80) are from transaction log nature.
    I still do not know how the original values are recovered, but I will study this out of curiosity.




    Thursday, July 12, 2018 7:57 PM
  • He said "There's nothing inaccurate in the log file. Period.". Like: stop talking, you are wrong, there is nothing to discuss.

    I happen to know Paul. I happen to know that Paul knows what he is talking about.

    I have been watching this thread, but not replied, since I have not spent too much time on the internal format of the log file.

    However, I believe myself to have some common sense. Let's say that you are actually on to something. What is, the information in the log file is incorrect. What could the consequence of that be?

    1) Database would be corrupted when a transaction is rolled back.
    2) Database would be corrupted when a transaction is rolled forward on recovery.
    3) Database would be corrupted when restored with full backup + transaction log applied.

    Corruption here includes all forms of changing of the data that should be there, not necessarily something that DBCC CHECKDB would discover.

    Thus, if you can present a repro where this actually happens, for instance you restore a database + log backups, and all of a sudden that table is now called c_datetime?ffset with that Chinese character in the middle of it. That would be very bad. Given the impact of such a bug, I would execpt that Microsoft has tested this code very well. And if there was a bug which you easily can walk into, there would be a lot of noise. But, hey, maybe you have been able to find a corner case where there actually is a bug. In such case, I would expect Microsoft to be very interested to learn about it.

    There is a second possibility: in your attempt to reverse-engineer there are things you have not yet understood the way they are.

    Thursday, July 12, 2018 8:27 PM
  • Yeah - I kind of thought that you'd respond like that although I hoped you'd be more mature.

    Oh well.

    Locking the thread now.

    If you still have fantasies about the LDF contents being inaccurate, please contact Microsoft Customer Support Services so they can help you.

    Thanks


    CEO & Owner, SQLskills.com (https://www.sqlskills.com/blogs/paul), Data Platform MVP, Author of 2005 DBCC CHECKDB/repair, Author/Instructor - Microsoft SQL Server MCM Certification

    Thursday, July 12, 2018 8:47 PM
    Moderator