I had written a stock market analysis program before. One of its tables had a SMALLINT column. When I tested LogCarver against that table, every row through it came back empty.
After finding that, I tested a batch of column types my own program rarely uses, plus a few types I had never specifically tested before. Turned out it wasn't just one missing type. Ten fixed-length types were missing: tinyint, smallint, bit, uniqueidentifier, money, smallmoney, datetime, smalldatetime, float, real. Any table using one of these lost that column's data, silently, no error, just nothing there.
Adding them back, same old method: go back to fn_dblog, check the byte format against a real instance, one type at a time. A few things turned out different from what I expected.
bit columns pack into a single byte, up to eight. I verified this by checking sys.system_internals_partition_columns: two different bit columns on the same table reported the identical leaf_offset. Their values sit in bit 0 and bit 1 of that one byte.
The old DATETIME type (not DATETIME2) stores time as 1/300-second ticks since midnight. The problem is 300 doesn't divide evenly into SQL Server's 10,000,000 tick resolution. Multiply before dividing versus divide first, and you get a real, measurable difference. I measured it at about half a second off.
While adding this batch, I also caught something unrelated to the new types: fn_dblog's own RowLog Contents columns cap out around 8000 bytes. A large enough in-row value can push a row past that cap. The old code treated that as corruption and dropped the whole row. Now it just flags the one column that went over and decodes the rest normally. All of this, plus the ten types, is in v0.1.12.
Source and more detail: https://github.com/caiderek/LogCarver
Full background on LogCarver, why I built it and how I reverse-engineered the fn_dblog format: How I Recovered Deleted SQL Server Rows Without Ever Enabling CDC or Audit (https://dev.to/caiderek/how-i-recovered-deleted-sql-server-rows-without-ever-enabling-cdc-or-audit-m10)
The bit packing, the DATETIME tick math, and the RowLog Contents 8000-byte cap were worked out with AI.
Top comments (0)