4

I have a two step job running periodically in SQL Server 2012.

  1. ALTER FULLTEXT CATALOG [CatalogName] REBUILD
  2. ALTER FULLTEXT INDEX ON [tblname] START FULL POPULATION

I didn't get the purpose of second step because google says that on first step itself sql server recreates catalog and generates indexes.

I would appreciate if someone could help me in understanding what happens internally during the execution of above two steps.

gofr1
  • 15,741
  • 11
  • 42
  • 52
Tanu Jain
  • 107
  • 1
  • 3
  • 11

1 Answers1

5
ALTER FULLTEXT CATALOG [CatalogName] REBUILD

Deletes catalog and creates new. If this catalog has many indexes this rebuild could take a long time.

ALTER FULLTEXT INDEX ON [tblname] START FULL POPULATION

Rebuildes only indexes on one of the tables.

If You have few indexes in one catalog both steps are equal. But if size of Catalog is much bigger and consist of many indexes, the first step would take much more time to run.

gofr1
  • 15,741
  • 11
  • 42
  • 52
  • We have one to one mapping. One catalog contains one index corresponding to one table. I think in that case i can remove either of the two steps. Do we have performance benefit of one over the other(i have around 208386 rows currently)? – Tanu Jain Feb 13 '16 at 16:51
  • In that case this 2 steps are equal. – gofr1 Feb 13 '16 at 18:17
  • Initially i thought the two steps are taking a lot of running time and transaction log space. I removed step 2 and then tried to run the job again. It took more than 90 GB of transaction log space and still running for only 2 lakh rows. Also the same same job is running on another server in just 10 seconds. Can you please suggest how can i reach the root cause or what could possibly be hampering this job? – Tanu Jain Feb 14 '16 at 03:38
  • Don't interrupt the process and allocate the necessary log. Don't let it auto grow, it will slow down the process. You need to pre-grow transaction log space to 2-3 times the size of the table. Rebuild takes a lot amount of log. – gofr1 Feb 14 '16 at 06:46
  • If you stop the job, that alter catalog, try to drop and re-create it manually, for some reason that could be faster than rebuild. – gofr1 Feb 14 '16 at 07:36
  • Would restarting the server help? Or i need to drop and create catalog and indexes again because from buisness perspective it would be risky. – Tanu Jain Feb 14 '16 at 09:35
  • Run this `SELECT FulltextCatalogProperty ('youcatatlogname', 'PopulateStatus')`. What digit returns this query? No need to restart. – gofr1 Feb 14 '16 at 10:08
  • it is returning 0 currently. – Tanu Jain Feb 14 '16 at 12:04
  • That means Idle popolation status. Job had finished? You stop it, o restatr server? What is the status of your situation with alter catalog? – gofr1 Feb 14 '16 at 14:54
  • I restarted the system now. Now jobs seems to work fine and running in less than 10 seconds. – Tanu Jain Feb 15 '16 at 10:24
  • Maybe something was blocking you job. Anyway, I'm glad you done with it. :) – gofr1 Feb 15 '16 at 10:31