Attempt to access expired blob handle (3). SQL 2005

[moonshadow]

I was just wondering if anyone could help with the following problem. A batch process, that has been running fine, has started failing with 'Attempt to access expired blob handle (3).' and 'A system assertion check has failed', causing SQL Server to write a dump file, and this message in the error log. Having searched the internet I found this problem might be fixed in SQL 2005 SP3, but having applied the patch, we're still getting the same error. So I ran a DBCC CHECKDB, and found two problems with the sys.sysobjvalues table in the database. To correct these I had to use the 'REPAIR_ALLOW_DATA_LOSS' option. However, the error is still occurring. My current thoughts are that the corrupt data was a compiled stored procedure, but I've recompiled the likely candidates to no avail. I'll attempt to recompile all the SPs and functions in the database tomorrow. I've tried running a trace, but the batch process is very long, and I haven't managed to capture the statement causing the problem yet. The process is being triggered by Service Broker, and the problem doesn't occur when running manually (but then, not calling all the same SPs).
Sorry to be a bit vague, but I'm at home, and all the information is at work.
Any suggestions, would be most welcome.
Pete Cousins

 

[moonshadow]

A quick update - I've found the problem is in one stored procedure. The trace captured the fact that SQL server was recompiling the following statement inside the SP:
delete #matchGroupsCore
where ODSChannelId = @channelId
and ODSCoreId = @coreId
The paremeters are both int. However, after the recompile, the trace indicates that SQL is starting a statement (the delete?), but the textdata is blank - did it fail to recompile correctly?
2 subsequent runs indicate that the error occurred in the same stored procedure, but past this delete statement - possibly at the next line that needed a recompile (no trace data for those two).
This behaviour has got me really stumped.

 

[moonshadow]

Another update - the lastest failure is on a statement yet further on in the stored procedure. We're going to try and keep re-running it in the hope that eventually all the statements will work. Very bizzarre - never seen anything like this before.

 

[moonshadow]

We've found the solution, and just list it here for completion.
It transpires that the SP is failing on the same statement every time - but the previous statement varies depending on parameters. The statement that is failing is a DELETE from a table that has been dropped from the database. As this statement never runs successfully, it's not showing in the trace. This is generating a normal SQL Error - however the stored procedure is being invoked by Service Broker in some way that I don't fully understand. This bit is not able to handle the SQL Error, and somehow is causing SQL to exception. The table has been recreated, and all seems fine now.