By: Kun Lee | Updated: 2009-02-05 | Comments (1) | Related: > Full Text Search
Problem
I have several Full Text Search enabled databases and these databases contain several catalogs. Very often, I deploy these databases to many servers, so I need to know if these are deployed correctly and also find out as quickly as possible. I need to know the population progress as well, but using management studio is too slow and also very hard to find out how much the catalogs have been populated.
Solution
You can use a T-SQL Script to pull all the major information at once. The query below has been tested and works on SQL 2000, SQL 2005 and SQL 2008.
Checking by using SQL Management Studio
Before I explain how you get the information via T-SQL, this is typical way to look at the Full-Text Search catalogs by using SQL Server Management Studio.
Right Click on a catalog and you will get the general property information.
After that, if you click the "Tables/Views", you will get the information shown below. But very often you will have to wait awhile for this screen to populate depending on how busy SQL Server is and how the configuration was setup. In my case, I usually wait 10 to 60 seconds before the information is all displayed. If you run Profiler while pulling the "Tables/Views" information, you will see why it takes so long to pull. It runs many redundant queries to pull this information and a lot of it I don't even need. I often only need to check that "Track Changes" is set to Automatic or not.
If I have one database with one catalog, that is not much work, but if you have several catalogs and several databases this can take a long time and be very frustrating. Also this doesn't clearly show the progress either without doing some calculations.
Information to pull
I need to pull the following information.
- All the Catalog names under the database
- Table Name
- If the Catalog is Enabled or Disabled
- Change Tracking Option
- Populate Status
- Row Count
- Full Text Search Count
- Difference between total row count and Full Text Search Count
- Percent Completed
- Full Text Search Location/Path
Sample Result
This is how it looks. Since the results are wide, I broke this into two screen shots.
T-SQL Code
This is how I wrote the query to capture this information. I am sure there are other ways to pull this information, but this is one easy way to pull the data. This has been tested for SQL 2000 to SQL 2008. I have used cursors and several system stored procedures and also directly queried the system tables. In order to minimize the impact, I changed the transaction isolation level to read uncommitted to do dirty reads and avoid any potential blocking issues..
set transaction isolation level read uncommitted set nocount on declare @tbl sysname declare @cat sysname create table #temp_ca( TABLE_OWNER varchar(100), TABLE_NAME varchar(256), FULLTEXT_KEY_INDEX_NAME varchar(256), FULLTEXT_KEY_COLID int, FULLTEXT_INDEX_ACTIVE int, FULLTEXT_CATALOG_NAME varchar(256) ) create table #temp_status( Catalog varchar(64), TblName varchar(64), [IsEnabled] bit, ChangeTracking varchar(24), PopulateStatus varchar(64), RowCnt int, FTS_CT int, Delta int, PercentCompleted varchar(128), path nvarchar(260) ) insert into #temp_ca exec sp_help_fulltext_tables declare ca_cursor cursor for select TABLE_NAME, FULLTEXT_CATALOG_NAME from #temp_ca open ca_cursor fetch next from ca_cursor into @tbl, @cat while @@fetch_STATUS = 0 begin insert into #temp_status select cast (@cat as varchar(40)) Catalog , cast(object_name(si.id) as varchar(25)) TblName , cast(OBJECTPROPERTY(tbl.id,'TableHasActiveFulltextIndex') as bit) as [IsEnabled] , case isnull(OBJECTPROPERTY(tbl.id,'TableFullTextBackgroundUpdateIndexon'),0) + ISNULL(OBJECTPROPERTY(tbl.id,'TableFullTextChangeTrackingon'),0) when 0 then 'Do not track changes' when 1 then 'Manual' when 2 then 'Automatic' end [ChangeTracking] , case FULLTEXTCATALOGPROPERTY ( @cat , '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 is full. Paused.' when 9 then 'Change tracking' end PopulateStatus , si.RowCnt, fulltextcatalogproperty(@cat, 'ItemCount') FTS_CT , si.RowCnt - fulltextcatalogproperty(@cat, 'ItemCount') Delta , cast ( 100.0 * fulltextcatalogproperty(@cat, 'ItemCount') / cast(si.RowCnt as decimal (14,2)) as varchar) +'%' as PercentCompleted , ISNULL(cat.path, 'Check Default Path') from dbo.sysobjects as tbl INNER JOIN sysusers as stbl on stbl.uid = tbl.uid INNER JOIN sysfulltextcatalogs as cat on (cat.ftcatid=OBJECTPROPERTY(tbl.id, 'TableFullTextCatalogId')) AND (1=CasT(OBJECTPROPERTY(tbl.id, 'TableFullTextCatalogId') as bit)) INNER JOIN sysindexes as si on si.id = tbl.id where si.indid in (0,1) and si.id = object_id(@tbl) fetch next from ca_cursor into @tbl, @cat end close ca_cursor deallocate ca_cursor select * from #temp_status drop table #temp_ca drop table #temp_status
Next Steps
- By using the "WAITFOR DELAY", you can rewrite this query to provide the estimated time to finish population by capturing the data and wait for a certain amount of the time and capture the data again to compare the progress and provide the estimated time to finish.
- You can use the "Help With Full-Text Catalogs - Stored Procedures Available In SQL Server" article and Configuration Information Locations for Full-Text Indexing in SQL Server 2000 to get other information about full text search.
- You can also set an alert or trigger to send a notification when the configuration of percent populated is lower than the threshold that you setup for managing many servers.
About the author
Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2009-02-05
