Search stored procedure not created in usage DB

Question

Hi,

I have set-up search on my sharepoint production environment and I face looots of error messages in teh event log concerening store procedure dbo.search_GetRecentStats

I quicky search on my staging environment and find out that around 15-20 stored prcedure prefixed by "search" were created in the usage DB.  I tried to recreate the search service application but the problem persists

Any clue to recreate those stored procedures?  PowerShell may help me? 

thanks in advance

Answer 1

Hi,

Normally, if the Search Service Application ot created with success message and the crawl has less error, we may conclude that search is working fine. In your case, it seems the SSA did get created fine but after that while your content is crawled, there are problems.

Please start with a new SSA. And then make sure its content source is set properly to only the apps you want crawled. Once that is done do a full crawl. Once the full crawl is done, start investigating the crawl log errors and troubleshoot why those errors are happening. If you can resolve those errors, you will be able to resolve search problems.

---

Thanks, Soumya | MCITP, SharePoint 2010

Answer 2

Hi, The point is:

Search crawls perfectly and search works like a charm. But when the timer process garhers statistical data on search in the usage DB, the event appears in the event log.

So, the truth is elsewhere ;-)

Answer 3

Hi,

Please provide one or two repititive error's description from the event log.

---

Thanks, Soumya | MCITP, SharePoint 2010

Answer 4

Here they are

Log Name:      Application
Source:        Microsoft-SharePoint Products-SharePoint Foundation
Date:          19/03/2012 05:59:06
Event ID:      6398
Task Category: Timer
Level:         Critical
Keywords:      
User:          XXX
Computer:      XXX
Description:
The Execute method of job definition Microsoft.Office.Server.Search.Monitoring.HealthStatUpdateJobDefinition (ID 20b2c94f-fabd-46b1-8f48-4516cd008389) threw an exception. More information is included below.

Could not find stored procedure 'dbo.Search_GetRecentStats'.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Microsoft-SharePoint Products-SharePoint Foundation" Guid="{6FB7E0CD-52E7-47DD-997A-241563931FC2}" />
    <EventID>6398</EventID>
    <Version>14</Version>
    <Level>1</Level>
    <Task>12</Task>
    <Opcode>0</Opcode>
    <Keywords>0x4000000000000000</Keywords>
    <TimeCreated SystemTime="2012-03-19T04:59:06.792170100Z" />
    <EventRecordID>594492</EventRecordID>
    <Correlation ActivityID="{E26B6F87-4D32-4656-A38A-34343AE1E9B3}" />
    <Execution ProcessID="15692" ThreadID="18608" />
    <Channel>Application</Channel>
    <Computer>xxx</Computer>
    <Security UserID="xxx" />
  </System>
  <EventData>
    <Data Name="string0">Microsoft.Office.Server.Search.Monitoring.HealthStatUpdateJobDefinition</Data>
    <Data Name="string1">20b2c94f-fabd-46b1-8f48-4516cd008389</Data>
    <Data Name="string2">Could not find stored procedure 'dbo.Search_GetRecentStats'.</Data>
  </EventData>
</Event>

Log Name:      Application
Source:        Microsoft-SharePoint Products-SharePoint Foundation
Date:          19/03/2012 05:59:06
Event ID:      5586
Task Category: Database
Level:         Error
Keywords:      
User:         XXX
Computer:      XXX
Description:
Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below.

Could not find stored procedure 'dbo.Search_GetRecentStats'.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="Microsoft-SharePoint Products-SharePoint Foundation" Guid="{6FB7E0CD-52E7-47DD-997A-241563931FC2}" />
    <EventID>5586</EventID>
    <Version>14</Version>
    <Level>2</Level>
    <Task>3</Task>
    <Opcode>0</Opcode>
    <Keywords>0x4000000000000000</Keywords>
    <TimeCreated SystemTime="2012-03-19T04:59:06.792170100Z" />
    <EventRecordID>594491</EventRecordID>
    <Correlation ActivityID="{E26B6F87-4D32-4656-A38A-34343AE1E9B3}" />
    <Execution ProcessID="15692" ThreadID="18608" />
    <Channel>Application</Channel>
    <Computer>xxx</Computer>
    <Security UserID="xxx" />
  </System>
  <EventData>
    <Data Name="int0">2812</Data>
    <Data Name="string1">Could not find stored procedure 'dbo.Search_GetRecentStats'.</Data>
  </EventData>
</Event>

Answer 5

Hi,

In the general questions and answers forum, this post seems similar to your:

http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/thread/9ff7086b-6fd1-428a-9def-229f25c4c5d5

Special attention to PawnofGod's & darkstu's answers.

---

Thanks, Soumya | MCITP, SharePoint 2010

Answer 6

Hi,

ma,y thanks for this.  It was the time job "Search Health Monitoring - Trace Events"  that needed to be enabled and ran.