Archive for the ‘Full Text Search’ Category

Unique Stop List for Single Database in SQL 2005

September 17, 2009 Leave a comment

Unlike SQL 2008, in SQL 2005 we cannot define a unique stop list (Noise Word List) for each database. As this is controlled by the Language settings on Full Text Index defined in the catalog.

So lets say I want the stop list to only include words from following list:

a, an, the, I, am, us

Rest of the words must be indexed as per the requirements. Setting up a new instance for this one database was out of scope for budget and modifying the noise world list on SQL Server affected more then a single database. I was able to meet the requirements by manually rebuilding the index with new stop list then changing the list back. But I did not want to do this every month manually; so I decided to create SQL Server Job for this. Before job could be created following must be done:

  • Confirm the time Job should run, it cannot overlap with any other auto-populations for full text index.
  • The full text catalog in question cannot have auto-population schedule.
  • You will have to enable xp_cmdshell Extended stored procedure on the server.
  • Create two new text files, one called SQL_noiseNEU.txt which is copy of noiseNEU.txt in MSSQL.1\MSSQL\FTData\ and another text file called Simple_noiseNEU.txt with my stop list.

So I created a new job to run last day of the month at 6AM, 6:15AM, and 6:30AM. They reason I created three schedules was to execute each step of the job at different time. If I executed all three steps one after another I found SQL was too fast in starting step 2 before step 1 finished. So putting a small delay gives each step enough time to complete:

Step 1 (Executed at 6AM):
Replace the noiseNEU.txt in MSSQL.1\MSSQL\FTData\noiseNEU.txt with Simple_noiseNEU.txt.

Step 2 (Executed at 6:15AM):
Rebuild the catalog for the database. Please note when you change the noise word list you have to rebuild the entire catalog you cannot just rebuild one index.

Step 3 (Executed at 6:30AM):
Replace the noiseNEU.txt in MSSQL.1\MSSQL\FTData\noiseNEU.txt with SQL_noiseNEU.txt.

SQL Job Script Generated from SQL Server: Link.

Categories: Full Text Search, SQL 2005

SQL Server 2005 Full Text Search services failed to Start Up

May 29, 2009 6 comments

Right after setting up SQL Server 2005 cluster on Windows 2008; the FTE Services did not start up automatically. Following error gets recorded in the event log/cluster events:

Cluster resource ‘SQL Server Fulltext (InstanceName)’ in clustered service or application ‘VirtualClusterName’ failed.

Generic application ‘SQL Server Fulltext (InstanceName)’ could not be brought online (with error ‘1075’) during an attempt to start the service. Possible cause: the specified service parameters might be invalid.

Microsoft Engineer pointed me to KB936302, Problem #3; however said solution of install SP2 was not enough. The services still refused to come online, to make it so services started successfully we edited following registry entry:

Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSFTESQL$InstanceName\
Multi-String Value: DependsOnService
Value Changed from …




Rebooted each node; services came on successfully.

%d bloggers like this: