0

I just created a full text catalog :

  • Enable full text (right click on db => file => enable ful text indexing)
  • Create catalog (Storage => Fulle text Catalog => New Catalog => "FTCatalog" => OK)
  • Add column to catalog (right click on catalog => properties => table/views add table/columns , automatic => ok)

The table has 270k lines, I created the catalog this morning and still it has 0 Item Count and the populate status is 1.

The logs in [...]\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLFT0000800007.LOG doesn't show anything more than (i tried to recreate it 10 min ago)

2014-06-30 16:32:03.93 spid23s Informational: Full-text Full population initialized for table or indexed view '[XXX].[dbo].[XXX]' (table or indexed view ID '1771153355', database ID '8'). Population sub-tasks: 4.

All SPs are installed.

I tried with some smaller table, it's the same.

The service "SQL Server FullText Search" is running.

Nothing on the EventViewer.

Any idea ?

UPDATE

In Sql Server Log I have these two thing :

Changing the status to PAUSE for full-text catalog "XXX" (7) in database "XXX" (8). This is an informational message only. No user action is required.

Changing the status to PAUSE for full-text catalog "XXX" (7) in database "XXX" (8). This is an informational message only. No user action is required.

0

You might try scripting it out to see if you get a different behaviour. This simple example runs in under a minute on my local SQL 2005 instance:

USE master
GO

SET NOCOUNT ON
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
BEGIN
    ALTER DATABASE fullTextDemo3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE fullTextDemo3
END
GO

IF NOT EXISTS ( SELECT * FROM sys.databases WHERE name = 'fullTextDemo3' )
CREATE DATABASE [fullTextDemo3]
GO

ALTER DATABASE fullTextDemo3 SET RECOVERY SIMPLE
GO

USE fullTextDemo3
GO

CREATE TABLE dbo.nearnessTest (
    rowId           INT IDENTITY,
    keywords        VARCHAR(MAX) NOT NULL,
    notes           VARCHAR(100) NOT NULL,

    CONSTRAINT PK_nearnessTest PRIMARY KEY ( rowId )
)
GO


INSERT dbo.nearnessTest
--SELECT TOP 270000 m1.[text], ''
SELECT m1.[text], ''
FROM sys.messages m1
--  CROSS JOIN sys.messages m2
WHERE m1.language_id = 1033
  --AND m2.language_id = 1033
GO

-- Create the catalog
IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ftc_List3' ) 
CREATE FULLTEXT CATALOG ftc_List3
GO

-- Create the full-text index
CREATE FULLTEXT INDEX ON dbo.nearnessTest (keywords) KEY INDEX PK_nearnessTest ON ftc_List3 WITH CHANGE_TRACKING MANUAL -- CHANGE_TRACKING OFF, NO POPULATION
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

DECLARE @i INT 
SET @i = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN

        SELECT outstanding_batch_count, *
        FROM sys.dm_fts_index_population
        WHERE database_id = DB_ID()

        --SELECT *
        --FROM sys.dm_fts_outstanding_batches
        --WHERE database_id = DB_ID()

    WAITFOR DELAY '00:00:05'

    SET @i = @i + 1
    IF @i > 60 BEGIN RAISERROR( 'Too many loops!', 16, 1 ) BREAK END

END

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO

SELECT * 
FROM dbo.nearnessTest 
WHERE CONTAINS( keywords, 'error' )

What happens when you issue an explicit start for the index population, eg

ALTER FULLTEXT INDEX ON dbo.yourTable START FULL POPULATION;

Run these summary functions

SELECT FULLTEXTSERVICEPROPERTY ( 'IsFulltextInstalled' ) IsFulltextInstalled
SELECT DATABASEPROPERTY('fullTextDemo3','IsFulltextEnabled') IsFulltextEnabled
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'AccentSensitivity ') AS AccentSensitivity 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'IndexSize ') AS IndexSize 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ItemCount ') AS ItemCount 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'LogSize ') AS LogSize 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'MergeStatus ') AS MergeStatus 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateCompletionAge ') AS PopulateCompletionAge 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'PopulateStatus ') AS PopulateStatus 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'UniqueKeyCount ') AS UniqueKeyCount 
SELECT FULLTEXTCATALOGPROPERTY('ftc_List3', 'ImportStatus ') AS ImportStatus
  • Thanks for the time you took for this script. I got : "Msg 50000, Level 16, State 1, Line 19 Too many loops!", so it's more a server problem than a database problem. – remi bourgarel Jul 1 '14 at 7:16
  • What service account is the FullText service running as? Maybe try LocalSystem or something else to see if the behaviour changes. – wBob Jul 1 '14 at 8:20
  • it was running with the same user as the sql server, I changed it with my credentials (i'm fully admin) and nothing changed – remi bourgarel Jul 1 '14 at 9:08
  • Might just be worth trying LocalSystem and a restart of the two services, full-text, then SQL Server service. Main answer updated with explicit start / fulltext functions. – wBob Jul 1 '14 at 9:34
  • Can't run with Local System, I got an error message "Logon failure: user account restriction. Possible reasons are blank password .... 0x8007052f" – remi bourgarel Jul 1 '14 at 12:18
0

I finnaly found something :

Reboot the server !

I absolutely have no idea why.

Thanks @wBob for your help.

  • It may be the SQL Server service restart implied by a full reboot helped here. Had you tried a SQL restart as suggested above? Oh well, glad it's resolved for you : ) – wBob Jul 2 '14 at 12:56
  • It's in a production server so I can't really try this now. – remi bourgarel Jul 2 '14 at 13:11
  • Sure no problem, I wasn't asking you to restart your SQL Server now, just asking if you had tried it before, as suggested in my comment yesterday. The reason I ask is I had a similar problem, and it was the LocalSystem and SQL service restart that fixed it for me. This was on my local dev machine so it was easy for me to do that. I understand it's not always so easy : ) – wBob Jul 2 '14 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.