Why is this population taking so long?

"sql411@nospam.com" wrote:

> what version of SQL Server are you running?
> I would check to see if you are having any errors:
>
> SELECT DB_ID(), * FROM sys.fulltext_catalogs
>
> Then you can go to your log directory (something like this: C:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG)
> and look for a file like SQLFT 000(db_id from above) + 000 (number of
> ftcatalog from above).log
>
> To see if it is moving along you can run the following and see the changes:
> select [table]=o.name
> , c.fulltext_catalog_id
> , c.name
> , i.rowcnt
> , missing=i.rowcnt-cast(objectpropertyex ( o.object_id ,
> 'TableFulltextItemCount' ) as bigint)
> , [%]= (cast(objectpropertyex ( o.object_id , 'TableFulltextItemCount' )
> as bigint)*100) / i.rowcnt
> , [status]=case objectpropertyex ( o.object_id ,
> 'TableFulltextPopulateStatus' )
> when 0 then 'Idle'
> when 1 then 'Full population is in progress'
> when 2 then 'Incremental population is in progress'
> when 3 then 'Propagation of tracked changes is in progress'
> when 4 then 'Background update index is in progress, such as autochange
> tracking'
> when 5 then 'Full-text indexing is throttled or paused'
> else 'UNKNOWN Status'
> end
> , [PopulateStatus Desc]= case FULLTEXTCATALOGPROPERTY( c.name ,
> 'PopulateStatus' )
> when 0 then 'Idle'
> when 1 then 'Full population in progress'
> when 2 then 'Paused'
> when 3 then 'Throttled'
> when 4 then 'Recovering'
> when 5 then 'Shutdown'
> when 6 then 'Incremental population in progress'
> when 7 then 'Building index'
> when 8 then 'Disk is full. Paused'
> when 9 then 'Change tracking'
> else 'UNKNOWN Status'
> end
> , objectpropertyex ( o.object_id , 'TableFulltextCatalogId'
> ) as TableFulltextCatalogId
> , objectpropertyex ( o.object_id , 'TableFulltextFailCount'
> ) as TableFulltextFailCount
> , objectpropertyex ( o.object_id , 'TableFullTextBackgroundUpdateIndexOn'
> ) as TableFullTextBackgroundUpdateIndexOn
> , objectpropertyex ( o.object_id , 'TableFulltextDocsProcessed'
> ) as TableFulltextDocsProcessed
> , objectpropertyex ( o.object_id , 'TableFulltextItemCount'
> ) as TableFulltextItemCount
> , objectpropertyex ( o.object_id , 'TableFulltextPendingChanges'
> ) as TableFulltextPendingChanges
> , objectpropertyex ( o.object_id , 'TableFulltextPopulateStatus'
> ) as TableFulltextPopulateStatus
> , dateadd(ss, FULLTEXTCATALOGPROPERTY( c.name , 'PopulateCompletionAge' ),
> '1990/01/01' ) as Age
> , FULLTEXTCATALOGPROPERTY( c.name , 'MergeStatus' ) as MergeStatus
> , FULLTEXTCATALOGPROPERTY( c.name , 'IndexSize ' ) as IndexSize
> , FULLTEXTCATALOGPROPERTY( c.name , 'ItemCount' ) as ItemCount
> , FULLTEXTCATALOGPROPERTY( c.name , 'PopulateStatus'
> ) as PopulateStatus
> , FULLTEXTCATALOGPROPERTY( c.name , 'UniqueKeyCount' ) as
> UniqueKeyCount
> , FULLTEXTCATALOGPROPERTY( c.name , 'AccentSensitivity'
> ) as AccentSensitivity
>
> from sys.objects o (nolock)
> join sysindexes i (nolock) on o.object_id = i.id and i.indid = 1
> join sys.fulltext_index_catalog_usages u (nolock) on o.object_id =
> u.object_id
> join sys.fulltext_catalogs c (nolock) on u.fulltext_catalog_id =
> c.fulltext_catalog_id
>
>
> hth,
> robert towne
>
>
> "Morgan" wrote:
>
> > I have a table full of part numbers VARCHAR(60) with about 3 million rows in
> > it. How long should the full index be taking? I've had to stop it after 5-6
> > hours because it's locking up the whole server.
> >
> > Is there something I'm doing wrong?



je ne comprends pas ces 2 tables:
join sys.fulltext_index_catalog_usages et sys.fulltext_catalogs
Pour la seconde, il doit s'agir de sysfulltextcatalogs, mais la première ???