Profiler 208 errors & the MVP monthly product issues list
One of the things I like most about the SQL Server MVP program is that MVPs are asked to submit a list of issues every month to our "MVP Lead" - Steve Dybing. Steve takes this list & publishes it to various people & even follows some of the better ideas up with the SQL Server development team members. Steve's a pretty good interpreter of good ideas on his own as he's been with Microsoft & the SQL team for 15 years and definitely knows the ropes over there.
Anyhow, I usually take a bit of time to think what issues I might submit each month because it's a great opportunity to get a message through to the SQL dev team about things I'd like to see improved with SQL Server. I'm going to start blogging these ideas here in case anyone's interested in either reading them or perhaps even contributing ideas. Of course there are no promises with this, but it's at least a channel for communication for anyone interested.
This month, I'm going to be submitting a request that the internal 208 errors thrown by the profiler be removed as these have really been getting up my snout lately. My constant error detection traces have been flooded with gazillions of these meaningless messages & it's making my already complicated life slightly more difficult. It's time to pipe up & see if something can be done. One of my constant error detection traces picked up ~13,000 error events last week alone, nearly ALL of which were these nasty little critters.
I've discussed this problem with other DBAs, MVPs & even some SQL dev team memebers over the past couple of years and it seems the source of the 208s is a design flaw in deferred name resolution. MVP Erland Sommarskog (Sweden) posted this to a recent MVP NG thread in October:
[i]
Unfortunately, a 208 in the Profiler does not have to be a real 208. This procedure will fire a 208 in Profiler:
CREATE PROCEDURE temptest AS
CREATE TABLE #abc (a int NOT NULL)
INSERT #abc ( a ) VALUES (23)
This is because when the procedure is parsed #abc does not exist. The exception is handled internally, all the vein of the (mis)feature known as deferred name resolution. This happens both in SQL2000 and in the lastest Yukon drop.
[/i] (used with permission)
It appears that the problem's continuing into SQL Server 2005, although I haven't tested this out on a recent build personally.
If there was some way of accurately filtering these blighters out of trace logs, the problem could be mitigated somewhat, but the danger with simply slapping a filter on the error data column is that you'd miss the REAL 208s which might be thrown by your app. Real 208s are serious problems because they can cause execution abortion mid-batch, orphaning transactions and potentially clogging up the db with dependant lock blocking chains. (I've seen this happen on real systems) So IMO, it's not really a good option to simply stick a filter on such as error!=208.
So this is my entry to the MVP monthly product issues list. I'd like to see the internal deferred name resolution use some other dedicated error number for reporting it's errors so that we can filter the real 208s from the internal 208s.