Sql-server – Reorganize full text catalog is offline or online

full-text-searchsql server

I need to schedule a Full Text Search Maintenance and I was reading this link: http://msdn.microsoft.com/en-us/library/ms176095.aspx. Reading this documentation I am supposing that I need to rebuild/reorganize the unique index used by the Full Text Search and then reorganize the Full Text Catalog. I am only concerning here, if this process of reorganize the Full Text Catalog is done online or offline? When the catalog reorganize is ocurring the users will still be able to query the full text data?

I appreciate any help here!

Regards,
Marcos Freccia

Best Answer

First of all, Welcome to stackexchange and thanks for your 1st Post.

To answer your questions :

if this process of reorganize the Full Text Catalog is done online or offline?

The reorganize process is Online, but is slower than Rebuild.

When the catalog reorganize is occurring the users will still be able to query the full text data?

Yes, but there might be blocking. Also you need to take account of below from BOL :

Depending on the amount of indexed data, a master merge may take some time to complete. Master merging a large amount of data can create a long running transaction, delaying truncation of the transaction log during checkpoint. In this case, the transaction log might grow significantly under the full recovery model.

Also, I would suggest you take a look at the fragmentation of the full text Indexes using :

I normally go for REORGANIZING if you have between 30-50 fragments per table

 -- for SQL Server 2008 and up   
SELECT OBJECT_NAME([table_id]) AS TableName, COUNT([fragment_id]) AS Fragments
    FROM sys.fulltext_index_fragments
GROUP BY OBJECT_NAME([table_id])
HAVING COUNT([fragment_id]) >=30

Also, Improve the Performance of Full-Text Queries is a good reference along with this whitepaper.