Products

Evoq Content Overview Content Creation Workflow Asset Management Mobile Responsive Personalization Content Analytics SEO Integrations Security Website PerformanceEvoq Engage Overview Community Management  Dashboard  Analytics  Member Profile Gamification Advocate Marketing Community Engagement  Ideas  Answers  Discussions  Groups  Wikis  Events Mobile ReadyDNN Support PackagesEvoq: CMS FeaturesEvoq OnDemandCompare DNN's CMS Products

Solutions

Content Management SystemMulti-Channel PublishingDigital MarketingOnline Community ManagementIntranet Software SolutionOur CustomersPrime

Resources

Test DrivesSchedule A DemoDocumentation CenterWebinarsWhite PapersProduct ManualsRequest PricingData SheetsCase StudiesEvoq Preferred Products Content Management Ecommerce Forms Identity Management and Authentication Site Management Social Themes

Partners

DNN Partners Partner Directory Become a DNN Partner Partner Program Overview  Hosting  Implementation  ISV  Training  Competencies

Community

Participate Ask a Question Forums Community Showcase DNN MVP  Program Overview  Lifetime MVPs  Honorary MVPs Subscribe to DNN Digest Advisory Groups  Awareness Advisory Group  Developer Advisory Group  Partner Advisory Group  Technology Advisory GroupLearn Documentation Wiki Community Blog Video Library Project History Development RoadmapDownload DNN Store Language Packs Manuals Nightly BuildsSecurity Policies Security Center

Blog

About

News Press Releases News Coverage Press KitCareersContact DNN

QA

Ideas Test

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...441 Search scheduler, deadlocks, can441 Search scheduler, deadlocks, can't clear tables - server dead
Previous
 
Next
New Post
1/30/2007 7:39 PM
 
ROB AX

Joined: 9/3/2004
Posts: 2185

Hi all,

Since the upgrade to 4.41, my logs have been filling up red with exceptions and failures, and I now believe they are clues to why my VPS account has been falling over once a day.

  • Lots of Violation of UNIQUE KEY constraint 'IX_SearchItemWord'.
  • Lots of Transaction (Process ID xxx) was deadlocked
  • always involving the DotNetNuke.Services.Search.SearchEngineScheduler

I have repeatedly cleared the search tables using the methods pasted further down.
I have also completely disabled the search engine scheduler task.

The problem is, the same errors still occur after the SearchEngineScheduler task has been disabled and the tables cleared? How?

So I looked into the db using Webbase Enterprise Manager at my host and the various searchitem and searchword tables are still full of data. Shouldn't they be empty?

How can I properly clear the tables out and how can I disable the indexer? When I run the scripts to clear the tables, it always reports success, but obviously it doesn't work.

My server is going to fall over again today unless I can figure this out  Any advice is greatly appreciated.

Rob

These are what I have used in attempts to clear out the tables:

delete SearchItemWordPosition where SearchItemWordId in (select SearchItemWordId from SearchItemWord where SearchItemID in (select SearchItemId from SearchItem)) 
delete SearchWord where SearchWordsId in (select SearchWordsId from SearchItemWord where SearchItemID in (select SearchItemId from SearchItem)) 
delete SearchItemWord where SearchItemId in (select SearchItemId from SearchItem) delete SearchItem 

and also the following queries in the order listed, pasted into Query Analyzer in the enterprise manager:

         delete dbo.SearchItemWordPosition
         delete dbo.SearchItemWord
         delete dbo.SearchWord
         delete dbo.SearchItem

The latter is from Ecktwo's handy site

 
New Post
1/30/2007 8:14 PM
 
ROB AX

Joined: 9/3/2004
Posts: 2185

Because the SearchEngineScheduler task was still banging away even though it had been disabled, I tried restarting the application in Host Settings.

That might have worked because the logs are now all purple with a dozen shutdowns and a bunch of restarts all within 15 minutes. I'm aware of that IIS restart problem problem being discussed in another thread, but the point here was that I think the search task may have finally stopped. I'll leave it going and see what happens.

The question remains how to effectively clear the tables out and get the search task started again without deadlocking? I think the source of the issue is that those scripts for clearing the tables out don't seem to work.

Any ideas still appreciated.
 
New Post
1/30/2007 8:26 PM
 
ROB AX

Joined: 9/3/2004
Posts: 2185

I tried pasting the longer table clearing script into the Enterprise Manager query analyser and listed the results. It said it only affected a handful of records, yet this is what's in those tables:

SearchCommonWords
dbo
8/10/2006 5:30:14 PM
PRIMARY
369
SearchIndexer
dbo
8/10/2006 5:30:14 PM
PRIMARY
1
SearchItem
dbo
8/10/2006 5:30:14 PM
PRIMARY
500
SearchItemWord
dbo
8/10/2006 5:30:14 PM
PRIMARY
19138
SearchItemWordPosition
dbo
8/10/2006 5:30:14 PM
PRIMARY
46500
SearchWord
dbo
8/10/2006 5:30:15 PM
PRIMARY
6918

I'm not up with working with databases, but I take that to mean there are over 70,000 records in there still.. I'd assume that the duplicate key errors are lurking in there as well.

rob

 
 
New Post
1/30/2007 10:07 PM
 
eck two

Joined: 11/15/2003
Posts: 364
I suspect you have umteen Search scheduler threads all running at once.  Once these threads are unleashed there is no way to stop them unless you reset iis or kill the aspnet process.  If you have control of the server, first stop your Search scheduler from running, then kill the aspnet process.  After that you can clear your search tables for sure.

Before turning the search scheduler back on, try re-indexing manually once and see how long that takes.
 
New Post
1/30/2007 11:03 PM
 
ROB AX

Joined: 9/3/2004
Posts: 2185

Hi ecktwo, thanks for the reply

I have the search task disabled and it's definitely no longer running because it says so and the log shows no errors. I can email the host to do stuff, but I haven't got remote desktop as such.

Does the whole site have to be disabled and offline in order to clear the tables or is it enough to disable the search task, then ask the host to reset IIS and then run the clearing script when the site starts up?

As an experiment, I tried running those four "delete dbo.SearchItem..." queries on my previous 440 db which is still sitting on the server with no domains pointed at its portals. The tables again remained full of data when I check them after the queries. I'm finding this very odd?

Thanks for any further assistance

rob
 
 Page 1 of 2
12Next 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...441 Search scheduler, deadlocks, can441 Search scheduler, deadlocks, can't clear tables - server dead


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out