April 16, 2008 at 11:39 pm
Hi All,
I have one table which has relation to more than 100 tables which holds the company details. I've cleared all related table data and tried to clear this table. But it is not allowing me to delete/truncate or drop the table and shows the message
" Msg 8621, Level 17, State 2, Line 1
The query processor ran out of stack space during query optimization. Please simplify the query."
Please help me...
April 16, 2008 at 11:52 pm
Drop the FK's
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 17, 2008 at 1:06 am
Thanks for the quickie.. 🙂
But this is not possible..
April 17, 2008 at 1:33 am
if you really want to drop the table, you can script the drop by right click on the table in Management Studio (SSMS)......
But it seems you have complicated things and written a delete statement which the SQL Server is having problem to resolve and execute.....can you give us the query you are trying to execute? We can try solving it....at least 'will have a better idea of the situation.....
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 17, 2008 at 2:08 am
Things are not complicated..
Everything is in straight line only...
I have a table Employees which has EmployeeID As Primary Key.
This table has foreign key reference to more than 100 tables.
When I am trying to clear the data in Employee Table with the query,
'Delete From Employee' , it throws this error message and not allowing to execute. I already tried the delete option in management studio. But there also I am getting the same error... Hope the scenario is clear to you...
April 17, 2008 at 2:12 am
the problem is the logical design of the db - having 100 FK's is extremely suspect as far as good design goes !
when the query processor compiles the
DELETE FROM childtable
it actually has to include all the code for triggers and to ensure DRI is not compromised
- ie there would be no orphan child rows without parents
if you use Query DisplayEstimatedExecutionPlan (ie Ctrl/L) in SSMS you will see the full extent of what it has to do
there is a limit of about 250 tables in one query statement (before you get compile errors), so that is why it is failing for you (not just the humble DELETE itself!)
some people have a propensity to over-index a table and to have unnecessary FK's in a db
- eg if T1 -> T2 -> T3 it is usually unneccesary to have T1 -> T3
get the logical design correct first and then the physical design and implementation will all work properly.
BTW because of all the domain checks as above a DELETE can be slow. e.g. if you have
create table Customers
(custid int identity(1,1)
,custname varchar(255) not null
,CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (custid)
)
CREATE TABLE Orders
(OrderID int IDENTITY(1,1) NOT NULL
,custid int NULL
,CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID)
,CONSTRAINT FK_Orders_Customers FOREIGN KEY(custid)
REFERENCES Customers (custid)
)
GO
then you should also have this (on child table) to help query optimiser (especially for DELETE)
CREATE NONCLUSTERED INDEX CustomersOrders ON Orders (custid)
GO
HTH
Dick
April 17, 2008 at 2:56 am
Hi ..
Thanks for the reply...
But the problem remains unsolved.. How can I remove the relationships if it is required there... ? Also is it possible to remove the relationship temporarily ?
April 17, 2008 at 3:24 am
my point is that it extremely UNLIKELY that all your FK's are REQUIRED
it is possible to temporarily disable a FK (for example to stuff in 200K rows)
- but when you re-enable it must check ALL rows for compliance
ALTER TABLE Orders NOCHECK CONSTRAINT FK_Orders_Customers
if you want help with analysing your 100 FK's suggest you post your DDL in this forum
- or post URL of an HTTP/FTP site where you have put it so people could [choose to] view
Dick
April 17, 2008 at 3:24 am
Remove.....delete.....re-create......simple....
else, check your where clause for the delete statement.....
and you can never truncate a table with imposed constraints on it....you have to have to remove the constraints for that.....
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 17, 2008 at 4:04 am
Dear Sanuj,
Try This....
First execute below listed 2 queries and store the results
Query1
SELECT 'ALTER TABLE [' + o2.NAME + '] NOCHECK CONSTRAINT ' + o1.NAME
FROM sys.foreign_keys o1
INNER JOIN sys.objects o2
ON o1.parent_object_id = o2.object_id
AND o1.TYPE = 'F'
AND o1.is_disabled = 1
UNION ALL
SELECT 'ALTER TABLE [' + o2.NAME + '] NOCHECK CONSTRAINT ' + o1.NAME
FROM sys.foreign_keys o1
INNER JOIN sys.objects o2
ON o1.parent_object_id = o2.object_id
AND o1.TYPE = 'F'
AND o1.is_disabled = 0
Query2
SELECT 'ALTER TABLE [' + o2.NAME + '] CHECK CONSTRAINT ' + o1.NAME
FROM sys.foreign_keys o1
INNER JOIN sys.objects o2
ON o1.parent_object_id = o2.object_id
AND o1.TYPE = 'F'
AND o1.is_disabled = 0
1. Execute first query’s result set to temporarily remove the checking with FK Constraint
2. Now Execute the Delete Query
3. Now execute the result set of the second query to add the constraint again
April 17, 2008 at 4:08 am
Thanks man...!!!!
It is solved....
Long live SSC Forum.....!!!
April 17, 2008 at 4:09 am
Thank you very much for the response.
This was really helpful and it worked.!!
I got a similar solution from another SSC member...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply