DBCC PAGE To The Rescue

Today, I was copying some databases from a SQL 2005 server to a shiny new SQL 2008 R2 server. And listening to Skrillex. As part of the operation, I was running DBCC CHECKDB on the databases after I restored each one. Now normally, I just use the NO_INFOMSGS, ALL_ERRORMSGS parameters, but since nobody was using these at the moment, I decided to add DATA_PURITY as well. To my utter astonishment (sarcasm), CHECKDB returned the following on one of them:

Msg 2570, Level 16, State 3, Line 1 

Page (1:2117757), slot 44 in object ID 78623323, index ID 1, partition ID 72057598521835520, alloc unit ID 72057598526029824 (type "In-row data"). Column "UPDATE_DATE" value is out of range for data type "datetime". Update column to a legal value.

Ouch. Note that if I hadn't been running DATA_PURITY checks as well, then I wouldn't have known about this until a user complained. To verify this, I ran the CHECKDB again without the DATA_PURITY option, and it came back with no errors. From what I understand, there is a bit more overhead when running DATA_PURITY checks, so if you can do this on a non-production environment, even better. For more info on the various DBCC options, check out Paul Randal's (blog | twitter) series on it: CHECKDB From Every Angle.

So I was ready to inform the application admin that there was a bad record in the database. But I can't just forward the error message to a non-DBA, who would be unfamiliar with the indicators above. I needed to be able to point her to the record in question.

Luckily, it was only one record, so I could look that up pretty easily, right?  Out of range is either below or above the standard datetime range for SQL, so I could just select MIN(CAST(UPDATE_DATE AS int)) from the table, or MAX, and see what each one returned, right? Wrong. Turns out SQL won't even try to convert it.  I also tried selecting the entire column, as INT to a temporary worktable to see if that would work. Nope. Lesson learned, there.

Then I remembered the handy DBCC PAGE command. All I needed was the page location, and the database name. and we should be able to locate the offending record. This worked even better than I imagined.

First, you have to turn on trace flag 3604 to get the output to come to the console instead of to the SQL error logs.

DBCC TRACEON(3604,1)
GO

Then I could run the DBCC PAGE command, using the database name, the page identifier shown in the beginning of the error message above, and the type of output I wanted to see.  There are four levels of output; 0,1,2, and 3, and I wanted the most detailed description of every row. So I chose option 3, and went for it.

DBCC PAGE('UserDatabase', 1, 2117757,3);
GO

This gave me an output that looked like this:

PAGE: (1:2117757)
*skip a whole bunch of stuff*
Slot 0 Column 6 Offset 0x4 Length 4 Length (physical) 4
ACCT_INT_CODE = 219                
Slot 0 Column 7 Offset 0x8 Length 4 Length (physical) 4
BAL_SK = 10011                      
Slot 0 Column 8 Offset 0xc Length 4 Length (physical) 4
CAL_SK = 104                         

Those are the individual row slots in the page, the column number and metadata.  Sweet. :-)  After a bit of searching for the column name in question, I came upon an entry that looked like this:

Slot 44 Column 4 Offset 0x28 Length 8 Length (physical) 8
UPDATE_DATE = INVALID COLUMN VALUE     

Perfect - now I could copy the rest of the information from that record into an email, and point the application admin to the exact record that needed fixed. If she can't fix it from the application side, I will attempt to update it from the database side. If that doesn't work for some reason, then we have the data, and can recreate the record, if needed.

Thanks,
-David.

Training Begets Training

Scene: A typical meeting room, with projector and a couple of people in it. One is me, one is the project planner, the third is my boss' boss.

(Fade in.)


Me: So let me demo this for you...
Boss: OK.
Me: (Spends the next 15 minutes or so reviewing the DBA dashboard and reports I created for my employer.)
Boss: Impressive. This is great stuff. Can we also get... (she details a few other ideas for reports.)
Me: Absolutely! I can get those created next week.

(And here's the good part.)

Me: By the way, thanks for sending me to that SSRS training a couple weeks ago. I wouldn't have been able to do about 90% of this as quickly without that.
Boss: Really?
Me: Yeah. I mean, I would have figured it out eventually, sure. But I just had the class, and being able to apply it immediately gives us something of value right away, as well as reinforcing what I learned.

(clock ticking)

Boss (thoughtful): So, do you have the link to the (vendor) course catalog I sent you?
Me: I sure do.
Boss: Good. Look through that and let me know if there are any other classes you want to take.
Me: Certainly, will do.  Oh, and I also have an interest in this other class.  It's by a different provider, but I think it would be well worth it.
Boss: Sounds good. Send me the details and we'll get it worked out.
Me: Will do.  Thanks!

(Fade to black.)

Life is Beautified - Share your desktop edition.

While I strive for simplicity for most of my time, I'm drawn to things that are complex, intricate works of art.  Usually this is more a function of me working on something similar. When I'm doing complex things, I like a complex environment. When I'm trying to simplify, I prefer a simple environment.

So here are my work and home desktops. Work is a bit complex right now, while home life is pretty calm and serene.  I'll let you decide which one is which. :-) The backgrounds were retrieved from DeviantArt, and the various info items are from the themes Blue Vision and Elegance, which are skins for the Rainmeter desktop enhancement application.

Also, if you're reading this, and have the time, I'd like to see your desktop as well. What is your environment like? Feel free to share in the comments below.

Thanks,
-David.

A Very Productive Day

I had three successive dreams in a row last night, where I was horribly late for my first day on the new job. Do you think my subconscious is trying to warn me of something?

So when I finally did get up, I resolved to get as much work done today as possible. K had a meeting to go to this morning, so I got the breakfast dishes cleaned up. I put out the kitchen trash, I mowed the back yard while E raked leaves in the front. After that, we all had a little apple snack. I bagged the leaves in the front, then K came home and we all straightened up the garage together.

Finally, in the afternoon, H and I took a trip to the local Meijer and picked up a few things. One of which was a shaving brush that I had been thinking about getting for a while. I used one a while back, when I was experimenting with different shaving methods for my face and head. I have now found a shave cream that I really like, and wanted to try it with a brush. Sadly, the first attempt was a bit of a failure. The brush had loose bristles, and felt very coarse on my face. However, I'm going to; first, give it a few shaves to see if it starts to soften up and second, remember that you get what you pay for. (It was the cheapest one, after all.)

I haven't had a day like this in some time - just haven't had the energy for it. I may have been just the teeniest, tiniest bit depressed about the job situation.  However, that's all been settled, and I feel really good about what I got done today, as well as a few other things that are going on:

I applied to a certain software company a while back, one of the few I would seriously consider relocating for. My app was rejected, but I ended up in a really nice email conversation with the person who reviewed it, who told me to keep them in mind if my skill set changes. I may just do that. It was a refreshing change from the standard HR brush off.

Ba-Con has invited me back to their convention again this year, to do one or more of my Skeptic's sessions. I don't know how much material they need yet, but I'm really looking forward to going back. I had a lot of fun last year and met up with the Airship Archon, a really nice group of Steampunk folks. I'm also considering working up a new presentation for this year, which will involve me overdosing on sleeping pills! (It's a homeopathy demonstration, so don't sweat it. I'll be fine. Trust me.)

I'm considering going back to podcasting. I'm waiting to see if a project I've been invited to participate in pans out, but if not, I'll probably start my own. I had an enjoyable run on CORICast, but it didn't end up being a long term project. A new one, if I would do that, would be mostly science, tech, games, and maybe some philosophical meanderings. Which is short for me talking about stuff that interests me. It needs a format and some work, but who knows, maybe someone other than myself would want to listen to it. :-)

In short, I see a lot of opportunities opening up for me right now. It would be foolish not to take advantage of them. Let's see what happens.

Thanks for reading.
-D.

Single Point of Failure.

Yesterday, on Twitter, I asked for suggestions on a voice recorder for use on car trips. I tend to drive a lot by myself and I think out loud, which is really just talking to yourself, and tends to keep other motorists away. I have a phone that has a voice recorder function on it, but it's limited to 1 minute. It's really only meant for short notes, and since it takes 9 clicks (I checked) to record a voice memo, it's not really usable in the car. I'm a distracted enough driver as it is. I don't need the additional mess of playing with my phone en route. So, I was thinking of a decent, not top-of-the-line, but serviceable, one-button or voice activated voice recorder, that I could use while on the road. I figure at least a few other tweeps might have had some experience with them, so I put it out to the twitterverse.  I got about 4 or 5 answers back.  Every single one was, "Use your smartphone." 

I am not, and have never been a fan of all-in-one solutions. I remember when some of the first MFDs (Multi-Function Devices) came out.  Then you could scan/print/copy/fax... all from one machine.  It was a nice idea for small business owners, but the bundled convenience came with a price.  The scans were dirty and low-res, as were the copies.  Printing was horribly slow, and faxing was hit or miss.  What you got in convenience, you lost in quality. These things have improved over time, of course, but I still see the same pattern today. Specialized machines do their job very well, but multi-taskers tend to do things poorly. Jack of all trades, master of none.

In the case of the voice recorder, when you have a phone, camera, gps, voice memo and internet device, etc. if the phone breaks, you still have the rest, but if you do everything from your phone, then when that goes South, as mine has in the past, you lose all of that functionality. It creates a single point of failure. We strive to avoid this in our complex enterprise systems, so why do we accept it with our phones? I would rather have the relatively minor inconvenience of carrying around a small series of devices, than putting all my eggs in one basket, and losing everything in one move.

So... can anyone recommend a voice recorder?

Thanks,
-David.