DB Audit  Challenge #1  

And the Winner is….

First, I want to thank everyone that participated in the DB Audit Challenge, and appreciate your positive comments and feedback.  One reader thanked me for running this challenge.  Another was happy to stay engaged b/c he could not attend PASS.  Many of you were preoccupied with or attending PASS, so I hope to provide more challenging challenges soon.    

I’ve received some interesting replies, and some very subtle questions like, ‘how do I figure this out?’, and ‘it’s impossible to read the transaction log without one of those products’.  Some more creative replies just dumped the whole thing into an online HEX editor, and just sort of did one of those word searches to find the answer.

But, one of the best, and winning answers, was definitely well-thought out and wholly complete.  While I asked for the contents of at least in one of the field columns presented, I asked for the whole data row to be constructed from the hexadecimal output provided.  This fine SQL DBA, not only gave us the correct answer, he broke it down exactly how it should be and explained how he figured it out. (See above reply, now that fellow will know how to figure it out 🙂 

Thanks for playing and hope to have the next challenge coming your way soon.

So, without further adieu, the Winner of the first DB Audit Challenge is:  drum roll, please!

Michael Lato.  Congratulations! Michael is a SQL Server Specialist at DataTamer Inc., hailing from Canada.  He has more than 10 years of experience consulting on various database systems with a special focus in the CRM (Customer Relationship Management) field. Michael's past roles have been as diverse as system designer, project manager and head trainer.  Michael is currently a member of the Quest SQL Server Expert Panel and he speaks regularly at SQL conferences such as PASS and SQLTeach.  He is also open to new sql server opportunities, so if you can use his services, feel free to reach out to him at michael.lato@datatamer.ca

Michael ‘s comments, along with his answer on this contest were:

‘ Well, that was an interesting challenge.  This was a deeper dive into the transaction log than I’ve ever done before and the hexadecimal conversions threw me for a little bit until I found the endian references.  Here are my results and the codes I extracted to get them:

ItemNumber: 103109

ItemName: OctHoliday

CatalogSkew: SCARY10101

ItemDescription: Happy Halloween

EntryDate: 2009-10-31 22:11:39.963

NoAvailable: 1

Source: RowLog Contents 0

0x30003200C59201004F6374486F6C6964617953004300410

05200590031003000310030003100C5C06D01B39C000001000

0000600C00100570048006100700070007900200048006100

6C006C006F007700650065006E00

Source Breakdown:

0x - header

30 - status A

00 - status B

3200 - position of number of columns in data row (50) required byte swap

C5920100 - ItemNumber,int4 (103109) required byte swap

4F6374486F6C69646179 - ItemName,char10 (OctHoliday)

5300430041005200590031003000310030003100 - CatalogSkew,nchar20 (SCARY10101)

C5C06D01B39C0000 - EntryDate,datetime8 (2009-10-31 22:11:39.963) required byte swap

01000000 - NoAvailable, int4 (16) required byte swap

0600 - number of columns in data row (6) required byte swap

C0 - null bitmap ()

0100 - number of variable-length columns (1) required byte swap

5700 - offset, end of first variable-length column (87) required byte swap

480061007000700079002000480061006C006C006F007700650065006E00 - ItemDescription,nvarchar300 (Happy Halloween)’

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

For SQLCentric Monitoring and Remote DBA Services goto http://www.pearlknows.com

SQLCentric now supports SQL Server 2008!