Chris Miller Blog

RocketScientist's Miscellaneous Ramblings

Error: 3624, Severity: 20, State: 1

This is a nasty error. It's an assertion (SQL Server Assertion: File: , line=1378 Failed Assertion = 'm_offBeginVar < m_SizeRec'.). If you do a search on Google, it looks like it's a really odd data corruption problem.

So I took the query in question (a really long, drawn out select statement from a web page. Don't ask me why it wasn't in a stored proc) and ran it in Query Analyzer. QA ran the query fine. Same results as what the web page should have displayed. If this was genuinely a data corruption problem, I'd expect the query to blow up in QA as well as from IIS. Hmm. A mystery.

I fired up Profiler. Love the profiler. Remember SQLEYE we had during the 4.21 days. Ah, nostalgia. As a sidebar, SQLEYE used to let the administrator "hijack" particular spids inject error messages back to the user. So we always used to send high-severity messages of "you suck" to each other. Real mature. At any rate, SQL Profiler didn't show the query coming in from the web server. OK, this is getting really strange.

I had a developer look at the web page. He saw some questionable things (above and beyond the fact that the page was sending dynamic SQL instead of stored procs) on the page. The page opened the connection, used a recordset (which worked fine) and then without closing the recordset it reopened the recordset with a new query (the one that bombed). The developer dropped in a set rs=Nothing on the webpage, and the problem magically went away.

My theory on what was happening was that the actual error wasn't a data corruption problem in the database, but a data corruption problem in the bit of memory that SQL Server uses to store inbound queries, probably because the query text was corrupt. I think the query text was corrupt because when the second query was executed the memory object on the IIS server that handled the query wasn't reset properly before it was reused.

Bear in mind that SQL Server does ALL memory allocation in pages and extents, even the bits that are actually network code, so if you somehow put corrupt data into a query you can crash the connection. An excellent DOS attack against the server, because the server has to do a memory dump and it closes the actual connection, so if you dump a few thousand of those to the server in a short period of time, you'll bog the server down pretty bad.

The moral of the story? Don't reuse recordsets that way. Do everything in stored procs. And, of course, don't allow non-trusted access to your sql server's network ports.

Legacy Comments

re: Error: 3624, Severity: 20, State: 1
I'm running into the same problem with a Stored Procedure execution from DTS.

re: Error: 3624, Severity: 20, State: 1
your problems are the statments sql, see

re: Error: 3624, Severity: 20, State: 1
I'm getting the same issue with a stored procedure executed from within the SQL Query Analyzer, but only sporadically. I'm performing analysis on stock numbers, and using one procedure to drive the process of analyzing multiple trading days at a time. After a few days of analysis, the procedure fails. If I just execute again, starting with the failed date, the process proceeds again until it dies with the same error. The offending stored procedure text? You ready for this?

CREATE PROCEDURE Analysis_PreTradingDay_CalcStuff
@rangeFiftyBeginDate datetime,
@rangeEndDate datetime
TRUNCATE TABLE _FiftyTwoWeekHighMovingAverage

INSERT INTO _FiftyTwoWeekHighMovingAverage (Symbol, FiftyTwoWeekHighSum, FiftyTwoWeekHighCount)
SELECT Symbol, SUM(FiftyTwoWeekHigh), COUNT(FiftyTwoWeekHigh)
FROM PreTradingDayAnalysisHistory
WHERE MarketDate BETWEEN @rangeFiftyBeginDate AND @rangeEndDate

The procedure used to just calculate the AVG, but it was failing similarily with a fatal error and "Connection broken".

Once it dies, I just reexecute the same trading day and it continues on for a few more. Bizarre.

re: Error: 3624, Severity: 20, State: 1
We are having the same problems when we use linked server and try to run triggers across 2 linked servers. Any idea why/how this issue can be addressed.

Jonathan Bays
re: Error: 3624, Severity: 20, State: 1
I ran a query in Query Analyzer that worked this morning without any problems. When I tried to run the same query in the afternoon, I started receiving this error message. I'm querying data in two databases on one server.

Jonathan Bays
A nasty development
I limited the query to a simple three column select statement on one table in one database and, depending on the order of the columns, the query would alternate between working and failing. It nearly always failed once I expanded the columns to five or more. This is particularly strange behavior given that I normally run 100+ column queries on the table. Perhaps the table has become corrupted in some way...?

re: Error: 3624, Severity: 20, State: 1
Same problem here.. We use ASP pages for our ICode Everest Enterprise accounting software which make extensive use of recordsets.

Also seeing some odd errors in EM now about ODBC Installer DLL not installed on this machine...

re: Error: 3624, Severity: 20, State: 1

re: Error: 3624, Severity: 20, State: 1
It happened only once during dbcc checkdb execution within the job.

re: Error: 3624, Severity: 20, State: 1
Am getting it during a create index stmnt

re: Error: 3624, Severity: 20, State: 1
got it on a DBCC CHECKIDENT(tbl, RESEED, 1)
seems to be a SQL Server BUG!

re: Error: 3624, Severity: 20, State: 1
..upgrading to SP4 should fix the problem!
(did it for me...)

re: Error: 3624, Severity: 20, State: 1
Same error when the Log Reader Agent Fails in Replication. "Cannot execute sp_replcmds".

We are on Service Pack 4 with the 2040 hot fix. I had Microsoft support involved a while back when we were on SP3A and he had me apply a hot fix for the problem then. It went away until we applied SP4, now it is back.