Post reply

converting hex data to char

  • October 1, 2007 at 2:53 pm

    #177871

    Hi all,

    I am trying to get information out of my transaction log by runnning this undocument dbcc log(dbid, -1) it return a bunch of rows with many column in some kind of Hex format. Does anyone know how to convert these kind of data back to char? Here is a sample output of a column name rowLogContents0, there are a few columns that have data like these. I assume this is the actual content of data been written to transaction log. I have tried to convert these data myself, but not getting anywhere. Please help..

    Thanks. Louis.

    NULL

    NULL

    NULL

    NULL

    0x460046008600860090009000A000A000

    NULL

    NULL

    0x30000800000000002D000000000000002C007A007A007C007E00820086008A008E009800A200AC00AC00B000B400B800BC00C600

    E200EC00F600000110011A012A012E01380138014C015C015C015C015C015C015C015C016C016C016C01760180019C01A601B601D

    20180200C0052A505418A84EC03191CDFA10100010000010000FFFFFFFF80000000000000000000000000000000000000000000000

    000000000000000000000000000000001000000000000FFFFFFFF000200000000000000000000000000000000000000000000000000

    00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

    000000000000000000000000000000000005E72EC56B51A354AAB0C5B1E74D1E97E00000000000000000000000000006C006F00750

    0x00000000000000000000

    0x00000000000000000000000000000000

    NULL

    NULL

    0x160068656C6C6F20616761696E

    NULL

    NULL

    NULL

    NULL

    NULL

    0x30002700320000000B0000000200000000000000000000000000000000000000000000000000000B0000F8010061005F00

    570041005F005300790073005F00300030003000300030003000300034005F0030003000300030003000300033003200

    0x36320000000B0000000300F8010042005F00570041005F005300790073005F00300030003000300030003000300034005

    F0030003000300030003000300033003200

    NULL

    0x300011003C320000000B00000000000000060060010022007F010000000000000000

    NULL

    0x10001A00320000000B0000000100000001000000040000000000070080

    0x60010022007F01000000000000

    0x00

    NULL

  • October 1, 2007 at 4:18 pm

    #737928

    because you are already in the undocumented world you should try:

    select [Object Name], [Index Name]

    from ::fn_dblog(null, null)

    Note that ::fn_dblog is also undocumented and probably a wrapper of dbcc log. The output is abit less cryptic but I would still stay a way from this for regular SQL Server operations


    * Noel

  • October 1, 2007 at 9:11 pm

    #737979

    What are you trying to do? We might be able to help another way.

  • October 2, 2007 at 8:53 am

    #738208

    I tried select * from ::fn_dblog(null, null) and the result are pretty much the same as dbcc log. What I am trying to do is be able to see the record that I updated or deleted before and after it happen. I believe it is in the transaction log. Unfortunally SQL Server doesn't offer anyway of auditing by table like Oracle. My boss does not want do to this with trigger or third party tools. I tried this and it doesn't work select cast(cast(0x170068656C6C6F20616761696E32 as varbinary) as varchar). I also tried UDF function I got of this site as well. I am sure there must be a way of converting this, Log explorer probably use something like this in their product, I think?

  • October 2, 2007 at 8:59 am

    #738213

    You'll need to use some type of third party tool, a trigger, or a separate insert if you control access to tables with procs.

    Those are the three options. I'd like to know why a third party tool won't work. If it's cost, you'll waste way more time trying to get this to work reliably than if you just get a tool.

    Log Rescue is free because it's being discontinued. ApexSQL and Lumigent make other products that aren't too expensive, but work great as well.

  • October 2, 2007 at 9:11 am

    #738221

    Steve,

    Thanks for that quick response, that is what I told my boss initially too. But, he really want to incorporate a feature where he can offer a way to show user what happen to an accidental update. Before and after the update. So user can decide what to do with the transaction. Is there NO way of converting those hex data to char as far as you know?

    thanks

    Louis

  • October 2, 2007 at 11:01 am

    #738284

    There probably is. I know some companies licensed code from MS to decode that stuff. Not sure if it's out in the wild yet. I'll ping a few people.

  • October 3, 2007 at 1:59 am

    #738512

    Hi Luis,

    it is not an easy task to do, but possible. The question is more along the lines "is it really worth?". It took me over 9 months for SQL Server 2000, the first half a year without documentation.

    Anyway, some of the basics: The transaction log records among many other things the data inserts and deletes completely, i.e. the whole data record. You may want to inspect individual pages with DBCC PAGE. The problem with modifications, is that the log contains only the difference. I.e. if you have a row that has an integer and a varchar, (1, 'foo'), and you change the varchar to 'fOo', the log record will only contain information about the change of 'o' to 'O'. This means that you cannot even create an easy update statement for this. In order to get all the data, you need to get the original row. If it has been deleted, table dropped, etc, this means that you need to find in the log the first insert, or go back to a full backup, dig out the whole log record, and then apply all the subsequent changes. If it has not been deleted, you can work your way backwards from the current row data, and apply the reverse operations. There are a couple of things on the way that make this process more interesting, such as page movements, binary large objects, out of row records, schema changes, etc.

    Also, DBCC LOG does not return all the log entries, only the ones that are part of the active log. In order to get all the log entries you would need to write an extended stored procedure to steal the transaction log from underneath SQL Server.

    A very useful resource if you decide to go down this way is Kalen Delaney's Inside SQL Server: The Storage Engine book. It contains a very good introduction to the internals.

    There are third party tools to read the log, so much of the above is taken care of if you only need to see what happened to your database. Hopefully you can decide whether you want to spend all your time on this for the next 6 months :D, if you need more details pm me.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • October 3, 2007 at 2:11 am

    #738514

    DECLARE @r VARBINARY(100)

    SET @r = 0x5065736F

    select cast(@r as varchar(100))

    N 56°04'39.16"
    E 12°55'05.25"

  • October 3, 2007 at 7:56 am

    #738617

    I pinged Dr. Andras. FYI, he worked on the Log Rescue tool.

  • October 3, 2007 at 10:02 am

    #738681

    Steve & Andras,

    Thank you very much for you help. I really appreciate it, the information you gave was very helpful. I will download Log Rescue tool to try out and see if we can use it in our environment.

    Thanks,

    Louis

  • February 22, 2010 at 6:06 am

    #1122877

    Steve Jones - Editor (10/3/2007)

    I pinged Dr. Andras. FYI, he worked on the Log Rescue tool.

    And did he bring some light into it?

    Greetz KSC

    Greetz
    Query Shepherd

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply