CODEWITHSUNDEEP
sql-servert-sqldebuggingstack-overflow
Daniel A. White
Daniel A. White

Reputation: 191037

Troubleshooting SQL Server Stack Overflow error

How can I effectively troubleshoot this error?

The query processor ran out of stack space during query optimization. Please simplify the query.

Msg 8621, Level 17, State 2

I've tried to attaching profiling, but I'm not sure I have the right messages selected. I do see the error in there. The Estimated Execution Plan gives this error as well.

The sproc I am calling is just doing a really simple UPDATE on one table. There is one UPDATE trigger, but I disabled it, yet it still is giving me this error. I even took the same UPDATE statement out and manually supplied the values. It doesn't return as fast, and still gives me the error.

Edit: OK, my generated script is setting the PK. So if I set the PK and another column, I get this error. Any suggestions along those lines?

Upvotes: 3

Views: 8853

Answers (4)

JNK
JNK

Reputation: 65197

There's a microsoft KB article about this.

Basically it's a bug and you need to update. I'm assuming you are running SQL Server 2005 sp2?

Upvotes: 4

Varan Sinayee
Varan Sinayee

Reputation: 1153

This error frequently appears when the number of foreign keys relating to a table exceeds the Microsoft recommended maximum of 253.

You can disable the constraints temporarily by the following line of code:

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

YOUR DELETE/UPDATE COMMAND

and after the executing your command, enable it again as the following:

EXEC sp_MSforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Hope that it helps.

Upvotes: 1

Josh Jones
Josh Jones

Reputation: 1

This isn't always a bug! Sounds like Daniel was able to come to the conclusion that the query wasn't as simple as he originally thought.

This article seems to answer a similar question as the one Daniel had. I just ran into the same error for a different (legitimate) reason as well. Dynamic SQL being run on a database with data no one anticipated resulted in a single select statement with hundreds of tables.

Upvotes: -1

Daniel A. White
Daniel A. White

Reputation: 191037

There are a great number of FK's that were being referenced by this PK. I changed our code not to update that PK any further.

Upvotes: 2

Related Questions