locked
SQL 2005 Full Text Index rebuild nightly best approach RRS feed

  • Question

  • I have a reporting table that is truncated and rebuilt nightly and a full text index on one of the columns. I have a job that truncates and rebuilds the table and after that runs I run the following sql code to populate the full text index:

    USE dbReporting;
    GO
    ALTER FULLTEXT INDEX ON FieldData
    start full population
    GO
    

    The index data is currently being created on the C: (system drive) and it was filled up this morning and I had to delete the catalog and am now in the process of trying to understand the best approach to (1) store this catalog on a different drive and (2) insure that each night I create the full text index on the new data correctly. I fear that the way I'm doing it may be appending and actually slowly creating a larger and larger index as the index files were up to about 50gb this morning for a table with 7 million records. The field that has the full text index on it is a nvarchar(4000) and the largest value in the table is 1,111 characters. I'm wondering if it would be better to not use an nvarchar datatype as well.

    In looking at the documentation it looks like the recommendation is to store the full text index in the same filegroup as the table. The table is on a separate drive, the E: drive, and there's plenty of free space on that drive (111gb).

    My question is how should I create and populate the full text index to have it be fully recreated every night with the new table data. Since this is a reporting table it is never edited and only needs to be populated once each night.

    I should be able to figure out how to have the full text index built on the same filegroup as the table. I'm not sure why it was built on the C: drive, though, as the documentation indicates that it will use the Primary filegroup and currently there is only one filegroup on the system. It looks like both the CREATE FULLTEXT INDEX and CREATE FULLTEXT CATALOG commands provide an option to specify the filegroup so I'll try that. 

    Any help and other tips appreciated.

    Thanks,

    John


    John Holmes Skagit County Mount Vernon, WA 98273
    Friday, October 21, 2011 5:19 PM

Answers

  • After some research and trial and error here's what I ended up doing and it appears to be working how I would like it to work.

    ALTER FULLTEXT CATALOG ftCatalog REBUILD
    GO
    ALTER FULLTEXT INDEX ON FieldData
    start full population
    GO
    

    The first statement basically zeros out the catalog and then I do a full population in the following statement. I was missing the first statement in my original code.


    John Holmes Skagit County Mount Vernon, WA 98273
    • Marked as answer by tunesmith Thursday, October 27, 2011 10:59 PM
    Thursday, October 27, 2011 10:59 PM

All replies

  • Hi John,

    Could you please provide the schema of the reporting table? How many filegroups do you have for the large reporting table? Have you used staging table through the ETL process? Have you partitioned that reporting table?

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, October 24, 2011 8:35 AM
  • At this point there is only one filegroup on the system but I'm not sure if it's being referenced. The database files reside on an E: drive on the system and I've currently moved the full text index files to the E: drive using the following command:

    create fulltext catalog ftCatalog IN PATH 'E:\FullTextIndex' as default;
    

    I did a script table as CREATE TO to get the schema into an easy to read format:

    CREATE TABLE [dbo].[FieldData](
    	[fdId] [int] IDENTITY(1,1) NOT NULL,
    	[TocId] [int] NOT NULL,
    	[TemplateId] [int] NOT NULL,
    	[TFieldId] [int] NOT NULL,
    	[FieldName] [nvarchar](63) NOT NULL,
    	[Value] [nvarchar](4000) NULL,
     CONSTRAINT [PK_FieldData] PRIMARY KEY CLUSTERED 
    (
    	[fdId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    

    The table is built using an SSIS package based on a query and is working fine. This step takes about 3 minutes. NOTE that each night this table is truncated and rebuilt from scratch. I don't believe I'm rebuilding/populating the fulltext index correctly. Maybe I need to drop it and recreate it each night. I haven't done any partitioning on this table and don't use a staging table.... john

     


    John Holmes Skagit County Mount Vernon, WA 98273
    Monday, October 24, 2011 3:29 PM
  •  I don't believe I'm rebuilding/populating the fulltext index correctly.

    Please refer to this online article.

     


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, October 25, 2011 1:50 AM
  • After some research and trial and error here's what I ended up doing and it appears to be working how I would like it to work.

    ALTER FULLTEXT CATALOG ftCatalog REBUILD
    GO
    ALTER FULLTEXT INDEX ON FieldData
    start full population
    GO
    

    The first statement basically zeros out the catalog and then I do a full population in the following statement. I was missing the first statement in my original code.


    John Holmes Skagit County Mount Vernon, WA 98273
    • Marked as answer by tunesmith Thursday, October 27, 2011 10:59 PM
    Thursday, October 27, 2011 10:59 PM