SharePoint Server Search toasted ... stuck on "crawling full"

Overview

It's the same old story. You wake up one morning and errors are flooding into the web server. This time it is your friend Mr. 10036. And he brought his SQL Server buddy 17310.

Event Type: ErrorEvent
Source: Office Server SearchEvent
Category: Gatherer
Event ID: 10036
Date: 9/15/2008
Time: 1:02:55 AM
User: N/A
Description: A database error occurred.
Source: Microsoft OLE DB Provider for SQL
ServerCode: 0 occurred 257 time(s)
Description: Unspecified error
Context: Application 'SharedServices'


Event Type: ErrorEvent
Source: MSSQLSERVER
Event Category: (2)
Event ID: 17310
Date: 9/15/2008
Time: 5:29:08 AM
User: N/A
Description:A user request from the session with SPID 58 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory.
Data:0000: 9e 43 00 00 14 00 00 00 žC......0008: 08 00 00 00 4d 00 4f 00 ....M.O.0010: 53 00 53 00 44 00 45 00 S.S.D.E.0018: 56 00 00 00 00 00 00 00 V.......


The short version is that the Office SharePoint Search Service is malfunctioning. The content source always believies it is "crawling full" even after web server and database server reboots.


Side note, the User Profile store also utilizes the Search Service to do its updating/querying. It believes that it is constantly "importing" profiles ... which it is not.

Solution

Here are some possible solutions that may work:

• Install SQL Server 2005 SP2 if it is not already installed
• Reset all crawled content (if possible)

Ultimately, we had to recreate the SSP to fix the problem ... this is not as scary as it sounds:

1. Go to Shared Serivices Administration
2. Select New SSP
3. On the New SSP page, you can select you current MySite web application IF you don't care about losing your user profile images.
4. The rest of the information for the New SSP page should be self explainatory ... if it isn't seek help.
5. After the new SSP is created you will need to reenter ALL values for ... everything. Don't worry your old SSP is still there and you can just pull it up in another browser tab and compare back and forth until you have it all filled in.
6. Next change the associations of the new SSP to associate with the port 80 and other sites/apps you have
7. Finally, change the new SSP to your default SSP
8. Run the search and profile import to confirm your issues are gone.

SQL Server Migration: 2000 32bit TO 2005 64 bit (Part 1)

Overview
This is the first post in a 3 part series on how to migrate from SQL 2000 32bit TO SQL 2005 64bit.

The first part will be a migration from SQL 2000 32bit to SQL 2005 32bit.

Notes

• Depending on what the Upgrade Adviser Tool tells you ... your steps may vary from those below.
• We also have aliased our DB using the Network Client Utility. This is not necessarily best practice, but it allows for easy migration and DB name changes.
  

Steps
1. Backup your SharePoint farm completely
2. Run the MS SQL 2005 Upgrade Adviser Tool
3. Delete excess backup history in the (Master>backupset table) that goes over 10,000 rows. (speeds up migration)
4. Quiesce the Farm.
5. Stop the WWW publishing service and all SharePoint specific services.
6. Check to make sure all sessions are killed.
7. Backup the SQL 2000 DATABASES.
8. Add the accounts for the application pools to the main Administrators group on the SQL 2005 box.
9. Move all SharePoint related logins to the new SQL 2005 instance.
10. Restore the SQL 2000 backup databases to the new SQL 2005 box.
11. Modify queries that use column aliases prefixed by table aliases in the ORDER BY clause. (what the Adviser told us)
12. Run the following stored procedure: sp_fulltext_service ‘load_os_resources’, 1 (what the Advisor told us)
13. On the web server go to Start > Programs > Microsoft SQL Server > Network Client Utility
14. In the Alias tab change the IP from the old server to the new server
14. Rebuild the indexes on the 2005 databases (what the Advisor told us).
15. Rebuild the statistics on the 2005 databases (what the Advisor told us).
16. Reboot the web server.
17. Restart all services.
18. Unquiesce the Farm.

Part 2 of this series will be changing the compatibility of the DB from 80 to 90. Part 3 will be moving from SQL Server 2005 32 bit to 64bit.

Content Deployment ... Violation of PRIMARY KEY constraint ...

UPDATE
Hotfix 950279 is available via contacting MS Support. After some "wrangling" ... this worked for us.

Overview

If you have just ran an "all content" deployment successfully ... don't start celebrating yet. Sure, it may have taken over a week with a hotfix or two to get it working. Just keep in mind that you will most likely want to setup deployment on a schedule for "only new, changed" content. That process turns out to be just as fun.

Issue

The first attempt at running a "only new, changed" content deployment (after a successful "all content" deploy) ended quickly with the following error:

Violation of PRIMARY KEY constraint 'PK__#ExportObjects____2F364608'. Cannot insert duplicate key in object 'dbo.#ExportObjects'. The statement has been terminated. at ...

Solution for pre-SP1 installations

Hotfix 936867 will fix this issue IF YOU HAVE NOT INSTALLED (the SP1s).

No Solution Yet for SP1 ...

If you have installed the SP1s, there is a much more drawn out solution. If you have previously installed 936867 and you install SP1 after ... your error will most likely return. Microsoft claims in their description of the MOSS SP1 ("2007 Microsoft Office servers Service Pack 1") that 936867 is included in the SP ... and this is it one of the "Issues that the service pack fixes" ... um LIE!

Great discussion about this issue here on MS's SharePoint forum.

Reporting Services ... Customizing the RSViewerPage.aspx Page

Overview ... Monday morning QB
Upon installation of Reporting Services there is a ReportServer folder created under the LAYOUTS directory that contains the .aspx files necessary to display reports. The primary file used for the display or reports is RSViewerPage.aspx. This page contains a large web part called ReportViewerWebPart. This web part includes both the toolbar/actions menu of the report, the report itself, and the parameters pane ... all in that one webpart. Mistake #1 by MS.

Another fun fact I noticed is that these pages are not child pages of a master. They are just stand alone .aspx files. Mistake #2 by MS. Hints, these pages do not include the global placeholders/navigation of the default.master nor the neighboring application.master (also found in the LAYOUTS directory).

When you deal with MS support these are they types of answers you get when you bring up such issues ... basically, wait till the next version.

Goal
Since by default the RSViewPage.aspx does not contain the global SharePoint navigation ... let's take a look at how to include it in the report page.

Solution
1. Make a backup of RSViewPage.aspx

2. Register the following at the top of the page:



3. Inlcude the following references inside the HEAD tag:



4. Place the following OnLoad event inside the BODY tag:
onload="javascript:if (typeof(_spBodyOnLoadWrapper) != 'undefined') _spBodyOnLoadWrapper();

5. Add the folowing lines of code:

• The ms-globalTitleArea TD class will contain the background image for the theme.
• The Logo.gif image represents the site logo
  
• Use id=onetIdTopNavBarContainer and class=ms-bannerContainer for the TopNavBar
  
• The wssuc:TopNavBar is the SharePoint top navigation.

That should do it!

I ran into some other issues that were installation specific ... so don't expect this to work perfectly for everyone ... but it will get you at least 90% of the way there.

64 bit database migration (the easy way)

DB Installation: Win 2k3 R2 SP2, SQL Server 2005 32 bit
Goal: Win 2k3 R2 SP2, SQL Server 2005 64 bit

If you are planning on migrating from 32 to 64 bit for your SQL Server ... look no further. After reading though blogs, white papers, and books ... I decided to that there was an easier way to handle all this nonsense. In simple terms:

1. Backup the databases on the 32 bit DB server (let's call it SharePointDBServer)
2. Take the web server offline, stop all services, sites, app pools
   
3. Take SharePointDBServer offline
4. Bring up a different 64 bit DB server with the same name as the old one (SharePointDBServer)
5. Restore the backups to the 64 bit DB server.
6. Reboot the web server
   
7. Restart any services on the web server that were stopped
   

That's it!

Keep in mind that if you have a DB server naming convention like SQL64SharePoint ... it probably won't work ... or if you cannot take the 32 bit DB offline ... that won't work either, etc, etc ...

If you have the means, this is ridiculously easier then trying this through STSADM ...

Things to remember:

1. Pay attention to the Owner of the databases after they are created on the new 64 bit server ... this will most likely be domain/Administrator ... which may not be the Owner of the previous DBs.
   
2. The services running on the new DB server will not be using the identities that were customized for the old DB server ... you will need to put in the correct user for some of those services.