SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

DB Audit Challenge #1 - Part II - Get a Clue

In the spirit of my Who DID IT and RAN SQL Caper presentation..... 

OK, I was hoping to get the creative juices stirring, and now I will provide some clues and direction on how to solve the T-log puzzle.  It's actually very simple (easy for me to say :-).  One just needs to know HOW to read the transaction log.  Come on, where's all the internals geeks out there?

First, to sweeten the pot, I will INDEED provide some prizes to be announced shortly - hey, I'm still waiting to hear back from the goody providers.

If you haven't yet seen the first entry on this, I invite you to take the DB Audit Challenge here:


Above, you will find the directions on how to play (no scratch-offs), and a schema, as well as the hexadecimal t-log content is all there for you to decipher the 'hidden' message.  So here are some clues, both technical and non-technical:

To read the Transaction Log output (see blog post above), you will need to find the position of the variable-length column called ItemDescription.  Since it is an nvarchar, it will be stored in Big-Endian order.  Find the position of the column, and convert it to an nvarchar - hey, I just told you how to do it - now you have to find it in there.  The goal is to reconstruct the entire data row (all column data based on the schem provided).  Since it is the ONLY variable length column in this schema, once you find the first position, the rest of the hex data will be the entire content needed to translate the data.

Oh, one final clue: It is a DELETED record, otherwise known as a 'Ghost Record'.  I completed and published this challenge on October 31, 2009.   Send all answers to sqlcentric@pearlknows.com - Good Luck!


Posted by Anonymous on 3 November 2009

Hey, just caught wind of some timely stuff going on at PASS. Had my trusty 'Google alert' email

Leave a Comment

Please register or log in to leave a comment.