Full-Text catalog properties - SQL Server

Recently I have been working with the Wiki project – just to prove that my desktop PC can be as fast as Windows Azure in hosting and searching through the entire Wiki content.

In order to do that I created a local database which contains all wikipedia articles in XML format and it uses XML indexing and Full-Text indexing to perform the search for keywords and also to rank the results.

The articles are already imported – there are 11,946,951 articles in the database. (There were a few articles which were not imported due to some XML error, but the ones that are imported will be enough to prove a point.)

So, right now I am populating the Full Text search catalog, and while I am doing that I can’t help but ask myself some questions:

  • how many rows are indexed?
  • how big is my Full Text catalog?
  • how do I know when the Full Text catalog is populated?
  • where is my Full-Text catalog saved?

And since I am a lazy kid, I like to answer my questions with T-SQL code and save the code for later reuse.

Here is some insight on the Full-Text properties:

 

declare @FTCatalogName nvarchar(250)
set @FTCatalogName = 'WikiDocumentFT'
 
SELECT
cat.name AS [Name],
cat.fulltext_catalog_id AS [ID],
CAST(FULLTEXTCATALOGPROPERTY(cat.name,'AccentSensitivity') AS bit) AS [IsAccentSensitive],
CAST(cat.is_default AS bit) AS [IsDefault],
dp.name AS [Owner],
FULLTEXTCATALOGPROPERTY(cat.name,'LogSize') AS [ErrorLogSize],
FULLTEXTCATALOGPROPERTY(cat.name,'IndexSize') AS [FullTextIndexSize (MB)],
FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount') AS [ItemCount],
FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus') AS [PopulationStatus],
0 AS [PopulationCompletionAge],
cast(null as datetime) AS [PopulationCompletionDate],
ISNULL(cat.path,N'') AS [RootPath],
CAST((select (case when exists(select distinct object_id from sys.fulltext_indexes fti where cat.fulltext_catalog_id = fti.fulltext_catalog_id and OBJECTPROPERTY(object_id, 'IsTable')=1) then 1 else 0 end)) AS bit) AS [HasFullTextIndexedTables],
FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS [PopulationCompletionDateInt],
FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge') AS [PopulationCompletionAgeInt],
GETDATE() AS [PopulationCompletionAgeNow]
FROM
sys.fulltext_catalogs AS cat
LEFT OUTER JOIN sys.filegroups AS fg ON cat.data_space_id = fg.data_space_id
LEFT OUTER JOIN sys.database_principals AS dp ON cat.principal_id=dp.principal_id
WHERE
(cat.name=@FTCatalogName)

And here is another script which will tell when the Full-Text catalog population was started:

SELECT  TE.name AS [EventName] ,
        DB_NAME(t.DatabaseID) AS DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.StartTime ,
        t.IsSystem
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), DEFAULT) T
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE   te.name = 'FT:Crawl Started'
        OR te.name = 'FT:Crawl Aborted'
        OR te.name = 'FT:Crawl Stopped'

This script is from the Default trace audit tool I developed earlier this year. You can download it from here: Default Trace Audit download.

 

And here is one more script, which elaborates on the status of the Full-Text catalog population:

 

DECLARE @CatalogName VARCHAR(MAX)
SET     @CatalogName = 'WikiDocumentFT'
 
SELECT
    DATEADD(ss, FULLTEXTCATALOGPROPERTY(@CatalogName,'PopulateCompletionAge'), '1/1/1990') AS LastPopulated
    ,(SELECT CASE FULLTEXTCATALOGPROPERTY(@CatalogName,'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 Full.  Paused'
        WHEN 9 THEN 'Change Tracking' END) AS PopulateStatus
FROM sys.fulltext_catalogs AS cat

To answer the question “Where the Full-text catalog is saved” is not that easy.
Starting with SQL 2008, the Full-text catalogs are part of the database engine and not saved on the disk system anymore.

The Full-Text catalogs in SQL 2012 are saved in one of the database filegroups.

 

Comments are closed.