dndorksadmin
asked on
Fixing SQL Server Log Error: 8623, Severity 16 (The query processor ran out of internal resources)
Some backstory first
I have an installation of Community Server 2.0 running with a SQL Server 2005 backend. After several years of use the database had become so large I could no longer back it up... Or at least not without taking my websites down. I wrote a delete query, but because of the 10+ foreign keys the delete statements were taking half an hour to an hour to delete less than 100 old posts.
I decided to get rid of the log, then wipe out a table called CS_SearchBarrel, which I knew would then cause the site to regenerate what it thinks are appropriate search terms, but hopefully would be much smaller (the search barrell was twice as large as the forum posts table!).
Getting rid of the log went tragically wrong and I ended up having to put the database into emergency mode and telling it to rebuild the database using dbcc checkdb (DBNAME, repair_allow_data_loss).
(Now the problem)
Since that point I've been getting very slow performance on the forums, which I believe I've isolated down to it rebuilding the search terms and the resulting error.
Error: 8623, Severity: 16, State: 1. Source: spid114 (this changes)
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
The Question
So my question ultimately is what are my next steps to get my database back on track? I don't know which query its dying on, or how to find it, so that would probably be a good place to start in diagnosing and fixing the problem, but maybe there is something more sweeping I could do. Alternatively maybe there is something I'm supposed to do after an emergency repair?
I have an installation of Community Server 2.0 running with a SQL Server 2005 backend. After several years of use the database had become so large I could no longer back it up... Or at least not without taking my websites down. I wrote a delete query, but because of the 10+ foreign keys the delete statements were taking half an hour to an hour to delete less than 100 old posts.
I decided to get rid of the log, then wipe out a table called CS_SearchBarrel, which I knew would then cause the site to regenerate what it thinks are appropriate search terms, but hopefully would be much smaller (the search barrell was twice as large as the forum posts table!).
Getting rid of the log went tragically wrong and I ended up having to put the database into emergency mode and telling it to rebuild the database using dbcc checkdb (DBNAME, repair_allow_data_loss).
(Now the problem)
Since that point I've been getting very slow performance on the forums, which I believe I've isolated down to it rebuilding the search terms and the resulting error.
Error: 8623, Severity: 16, State: 1. Source: spid114 (this changes)
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
The Question
So my question ultimately is what are my next steps to get my database back on track? I don't know which query its dying on, or how to find it, so that would probably be a good place to start in diagnosing and fixing the problem, but maybe there is something more sweeping I could do. Alternatively maybe there is something I'm supposed to do after an emergency repair?
James Murrell
http://support.microsoft.com/kb/917888 should fix
ASKER
Unfortunately I already found that and installed the latest hotfix. No luck. I did rebuild manually some of the indexes and it seems to have helped some, which gives me hope.
Maybe if I could find out which query its failing on I could focus on that one?
Maybe if I could find out which query its failing on I could focus on that one?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The maintenance plan did it. It wouldn't run live unfortunately, and took 3 hours to do, so my websites were down for 3 hours, but I took advantage of the downtime to clean out huge amounts of old data from years ago and the sites run faster than they have for years now. :D
Glad to be of assistance. May all your days get brighter and brighter.