Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Gathering status and detail information for SQL Server Full Text Catalogs


By:   |   Last Updated: 2009-02-05   |   Comments (1)   |   Related Tips: More > 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



Last Updated: 2009-02-05


get scripts

next tip button



About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, September 22, 2016 - 9:48:26 AM - Morris Back To Top

 Hi Lee,

Great Effort. I need some help when I run this on SQL 2012 it's not returning any rows back. I ran when FULL Text index population was in progress. 

I understand you have tested it on SQL 2005 and 2008, but do you see any issue while running this on SQL2012 ENT version?

Thanks for your time

 


Learn more about SQL Server tools