Menu
Got a requirement to rebuild mssql full-text index.
Problem is - I need to know exactly when job is done. Therefore - just calling:
ALTER FULLTEXT CATALOG fooCatalog
REBUILD WITH ACCENT_SENSITIVITY = OFF
doesn't work or I'm doing something slightly wrong. :/
Any ideas?
722
Speaking for SQL Server, I tend to choose a index size and index fragmentation level at which point I begin performing index maintenance. If an index contains less than 100 pages, I will perform no maintenance. If an index is between 10% and 30% fragmented, I will REORGANIZE the index and UPDATE the statistics.
A: You can determine if Full-Text Search is installed by querying the FULLTEXTSERVICEPROPERTY like you can see in the following query. If the query returns 1 then Full-Text Search is enabled.
Rebuilding an index means deleting the old index replacing it with a new index. Performing an index rebuild eliminates fragmentation, compacts the pages based on the existing fill factor setting to reclaim storage space, and also reorders the index rows into contiguous pages.
You can determine the status of the fulltext indexing by querying the indexing properties like this:
SELECT FULLTEXTCATALOGPROPERTY('IndexingCatalog', 'PopulateStatus') AS Status
Populate Status:
0 = Idle
1 = Full population in progress
2 = Paused
3 = Throttled
4 = Recovering
5 = Shutdown
6 = Incremental population in progress
7 = Building index
8 = Disk is full. Paused.
9 = Change tracking
But also pay attention to this note in the article:
The following properties will be removed in a future release of SQL Server: LogSize and PopulateStatus. Avoid using these properties in new development work, and plan to modify applications that currently use any of them.
EDIT: Corrected link to a newer page and added quote from the note
176
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
