Sp_altermessage “Classic”, much better than “New” sp_altermessage

I was reading through the SQL Server 2005 SP3 release notes yesterday (sadly, it was enjoyable) and clicked through to the Bugs Fixed link. After reading through the KB articles that I felt had direct implications to my current environment, I kept scrolling.

Third fix from the bottom is one of the more useful functions of SQL 2000 that was removed in SQL 2005:

“The sp_altermessage stored procedure can now be used to enable or disable system messages to the Windows event log in SQL Server 2005”

My mental radio immediately switched to Handel’s Hallelujah Chorus.

I remember my first time trying to send 229 errors to the event log on a SQL 2005 box. I had used this method extensively in the past to ferret out both legitimate and illegitimate permissions errors. When I ran the near instinctual statement:

sp_altermessage 229, with_log, true

I was greeted with an error message:

Msg 15178, Level 16, State 1, Procedure sp_altermessage, Line 20
Cannot drop or alter a message with an ID less than 50,000.

Much to my chagrin, I soon realized this wonderful tool had been stripped from my toolbox.

Evidently, in SP3, this was changed. Being the over-analytical paranoid skeptic that I am, I installed SP3 on my local Developer instance. I ran the command and received the three words every DBA loves to see:

Command(s) completed successfully.

So, for any of you who don’t think that the Connect website works, I’m pretty certain that Microsoft would have left this alone had it not been for the DBA warriors who kept this issue bubbling to the top.

As a side note, please forgive the pun regarding my least favorite soda drink.

OLEDB Wait Stats in SQL Server 2005

Over the past several months, I’ve started diving into the world of wait stats on my instances. An ever present phenomenon is that there are always a ton of waits with a wait_type of “OLEDB”. On my production servers, this count is always the highest and generally eclipses the closest second by a factor of two or three.

I was initially concerned, so I asked the “World’s Smartest DBA” (aka Google) and found little to help. I read the description from the horse’s mouth (http://tinyurl.com/3d2hym) and saw nothing too horrible nor helpful. Eventually, I chose to ignore it as an anomaly and went on in my blissful ignorance.

That was, until attending the PASS Summit. I had the pleasure of attending the “Corruption Survival Techniques” session where Paul Randal made a passing statement, which I’m certain most people didn’t even notice, that DBCC CHECKDB uses OLEDB to talk to the storage engine. A light went on and I had to do some tests as soon as I got back to the hotel room.

I took a snapshot of the waits from sys.dm_os_wait_stats. After recording the OLEDB waits, I ran an integrity check on an 8GB database. After the CHECKDB finished, I had racked up an additional 8 MILLION (yes, six zeros) OLEDB waits and a total of 65 seconds of wait time. The total integrity check only took 85 seconds. I ran it again, another 8 million waits.

Thinking I might have some obscure setting that is causing my waits to grow out of control, I asked Paul and he took the time to respond to my request for clarification. To my surprise, this is acceptable as long as CHECKDB isn’t completing in an unreasonable amount of time.

If you’re ever curious to know why OLEDB is racking up so many waits, you should see how many waits your integrity checks are creating. Although other issues can be hidden in OLEDB waits, you may be able to justify this wait stat down to an inconsequential value.

Workaround Primary Key Violations in SSIS

Before you get much further, please do not use this for every case where you have issues with duplicate rows. In most cases, the issue is in the source query and should be resolved there.

However, if you've encountered issues like I just did, this lazy DBA's approach might prevent you from banging your head against a wall.

I'm receiving a text file from a mainframe system. This file is fixed width and seemed easy to import. Unfortunately, the values in the file are duplicated and cannot be fixed.

I entertained the idea of staging tables and of redirecting the error rows, but eventually stumbled across the "Sort" transformation. There is a small checkbox in the lower corner that says "Remove rows with duplicate sort values". Voilà, no more duplicate key violations.

With the addition of a single transformation, the package is now just as simple as I originally had expected it to be.

My first blog post -- starting small

I've been hanging around the SQL PASS Summit this week and came to a realization, I do know something...

I don't know it all, but I don't know nothing, I'm definitely somewhere in between. So, I hope to share these thoughts and hopefully help out people in similar ways as I've received (primarily) and given (in small portions) this week.

I'm a SQL Server DBA and have every aspiration of keeping it that way. Through the lifetime of this blog (could be short; could be long) I hope that anyone who cares (all 3 of them, plus my kids) will walk away feeling a little more enlightened.

I'll start short.

One of the small tidbits I've gleaned this week is that doing what one would believe to be an innocuous shrink operation of tempdb can cause corruption; necessitating instance restart. The proper method is to bring the instance into single user mode first. For more information, hear it from the horses mouth: http://support.microsoft.com/kb/307487#6

Good luck and happy DBA'ing. :-)