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.