We help IT Professionals succeed at work.

Sharepoint 3.0 Search Index Fails to Rebuild

KellyOConnor
KellyOConnor used Ask the Experts™
on
My sharepoint site was showing documents in the search results that had been moved to another library so the link, when clicked, gave the "page cannot be found" etc. I read that the Index needed rebuilt so, from WSS 3.0 Administration I stopped the search service. I opened SQL Server Management Studio Express, connected to the sharepoint site and deleted the Search database. I also made sure that the actual log and index files were actually deleted from the HD.  Then, from WSS 3.0 Administration I attempt to start the service, keeping all the defaults. It begins creating new indexes but fails with this error: A SharePoint database named WSS_Search_SERVERNAME already exists.  You must supply another name for the new database. The thing is, before I run the start command, the search database does not exist and from within windows explorer, I can actually watch the database and log file being created. Also, from within the Management Studio the new Index shows up. In Central administration, the service is still not running. How can I get shareopoint search indexes going again?  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You should have used SharePoint Central Administration to delete the database.  Since you deleted it outside of SharePoint's realm, it doesn't know the status of the database and still references the old database in the configuration database.  You could have just performed a full crawl and it would have served the same purpose.  If you can. restore the DB where it was and then try performing a Full crawl which will rest all of the content in the indexes.

Commented:
Yep, or go into services in SharePoint Central Admin and stop the Windows SharePoint Services Search Service.  It will prompt you that you are in fact going to delete the databases.  Let it finish and then start search again, to go through the setup for recreating the search DB.
The command line equivalent for stopping search and resetting it (then go into central admin and start to recreate) is stsadm.exe -o spsearch -action stop
as per: http://blog.brainlitter.com/archive/2009/01/31/how-to-fix-broken-wss-crawlsearch.aspx 

Author

Commented:
Thanks for the reply guys. I thought I mentioned in my post that i did use Central Administration to "Stop" the search service. Instead I said: WSS 3.0 administration, my bad. I did get the warning about deleting and uninstalling. But now no matter what I try it will not start again. If I do nothing but click start I get this error: "WSS_Search_W2K3SERV01 on np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query contains user-defined schema.  Databases must be empty before they can be used.  Delete all of the tables, stored procedures and other objects or use a different database"
Steps taken: I run the stsadm.exe -o spsearch -action stop command line, rename the databases, Use SQL Management studio to remove them and then run  stsadm.exe -o spsearch -action start   it will begin to rebuild the indexes up to a point then error out saying they already exist even though I renamed them, and removed them from SQL using management studio. I have double checked the default location that they don't exist, and in the other drive they are still renamed.
 My content, logs and search databases are located on a different drive than C. But when I use the command line stsadm.exe -o spsearch -action start,   it creates the new search indexes in the default folder on drive C and still errors out. Central Administration says not to use the Network Service or the Local Service accounts. What accounts should be used? I am certain it was set on "Network Service" before I stopped it. Lemme see what you say to all this before I do much else. One final peice to the puzzle: A month ago, a Sharepoint patch failed to install and it left SP in an unuseable state. I called MS and they fixed it free since the patch killed it. Mabye my inability to start the service is related and I should just call them again. Ideas?
I finally had to pay the price and hire MS to fix this as I had exhausted things to try. It took them four hours to finally fix it. The final fix was to Un-Provision the "busted" database, delete it and then rebuild it. Finallly, Provisioning the new database is what seemed to fix it. Then, a Full crawl finally worked.  Below is the final command lines that fixed it for anyone that might get some use from them. I will award some points to each of you for at least responding to the question with good advise on each. Thanks guys!
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o execadmsvcjobs
Operation completed successfully.

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o enumservices > c:\services.txt
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o provisionservice -action stop -servicetype "Microsoft.SharePoint.Search.Ad
ministration.SPSearchService, Microsoft.SharePoint.Search, Version=12.0.0.0, Cul
ture=neutral, PublicKeyToken=71e9bce111e9429c" -servicename "spsearch"
Operation completed successfully.
Operation completed successfully.
If this is a Web service, IIS must be restarted for the change to take effect.
To restart IIS, open a command prompt window and type "iisreset /noforce".
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>iisr
eset
Attempting stop...
Internet services successfully stopped
Attempting start...
Internet services successfully restarted
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o enumservices > c:\services.txt
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o provisionservice -action stop -servicetype "Microsoft.SharePoint.Search.Ad
ministration.SPSearchService, Microsoft.SharePoint.Search, Version=12.0.0.0, Cul
ture=neutral, PublicKeyToken=71e9bce111e9429c" -servicename "spsearch"
Operation completed successfully.
Operation completed successfully.
If this is a Web service, IIS must be restarted for the change to take effect.
To restart IIS, open a command prompt window and type "iisreset /noforce".
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o provisionservice -action stop -servicetype "Microsoft.SharePoint.Search.Ad
ministration.SPSearchService, Microsoft.SharePoint.Search, Version=12.0.0.0, Cul
ture=neutral, PublicKeyToken=71e9bce111e9429c" -servicename "spsearch"
Operation completed successfully.
Operation completed successfully.
If this is a Web service, IIS must be restarted for the change to take effect.
To restart IIS, open a command prompt window and type "iisreset /noforce".
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o enumservices > c:\services.txt
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o execadmsvcjobs
Operation completed successfully.

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o provisionservice -action start -servicetype "Microsoft.SharePoint.Search.A
dministration.SPSearchService, Microsoft.SharePoint.Search, Version=12.0.0.0, Cu
lture=neutral, PublicKeyToken=71e9bce111e9429c" -servicename "spsearch"
Operation completed successfully.
Provision of service spsearch failed. Additional information: SPSearch (omt\admi
nistrator).
If this is a Web service, IIS must be restarted for the change to take effect.
To restart IIS, open a command prompt window and type "iisreset /noforce".
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o provisionservice -action stop -servicetype "Microsoft.SharePoint.Search.Ad
ministration.SPSearchService, Microsoft.SharePoint.Search, Version=12.0.0.0, Cul
ture=neutral, PublicKeyToken=71e9bce111e9429c" -servicename "spsearch"
Operation completed successfully.
Operation completed successfully.
If this is a Web service, IIS must be restarted for the change to take effect.
To restart IIS, open a command prompt window and type "iisreset /noforce".
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o provisionservice -action start -servicetype "Microsoft.SharePoint.Search.A
dministration.SPSearchService, Microsoft.SharePoint.Search, Version=12.0.0.0, Cu
lture=neutral, PublicKeyToken=71e9bce111e9429c" -servicename "spsearch"
Operation completed successfully.
Provision of service spsearch failed. Additional information: SPSearch (omt\admi
nistrator).
If this is a Web service, IIS must be restarted for the change to take effect.
To restart IIS, open a command prompt window and type "iisreset /noforce".
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o provisionservice -action stop -servicetype "Microsoft.SharePoint.Search.Ad
ministration.SPSearchService, Microsoft.SharePoint.Search, Version=12.0.0.0, Cul
ture=neutral, PublicKeyToken=71e9bce111e9429c" -servicename "spsearch"
Operation completed successfully.
Operation completed successfully.
If this is a Web service, IIS must be restarted for the change to take effect.
To restart IIS, open a command prompt window and type "iisreset /noforce".
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o provisionservice -action start -servicetype "Microsoft.SharePoint.Search.A
dministration.SPSearchService, Microsoft.SharePoint.Search, Version=12.0.0.0, Cu
lture=neutral, PublicKeyToken=71e9bce111e9429c" -servicename "spsearch"
Operation completed successfully.
Operation completed successfully.
If this is a Web service, IIS must be restarted for the change to take effect.
To restart IIS, open a command prompt window and type "iisreset /noforce".
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o spsearch -action start
The Windows SharePoint Services Search service was successfully started.

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsa
dm -o spsearch -action fullcrawlstart
Operation completed successfully.

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>