:::: MENU ::::

Tuesday, March 2, 2010

March 02, 2010

SQL Server FTI: How to check table status?

enter code hereIn the SQL Server Full-Text Indexing scheme i want to know if a table is in

  • start_chage_tracking mode
  • update_index mode
  • start_change_tracking and start_background_updateindex modes

The problem is that i set my tables to "background update index", and then tell it to "start change tracking", but then some months later it doesn't seem to be tracking changes.

How i can i see the status of "background updateindex" and "change tracking" flags?

example:

sp_fulltext_table @tabname='DiaryEntry', @action='start_background_updateindex'

Server: Msg 15633, Level 16, State 1, Procedure sp_fulltext_table, Line 364
Full-text auto propagation is currently enabled for table 'DiaryEntry'.

sp_fulltext_table @tabname='Ticket', @action='start_background_updateindex'
Server: Msg 15633, Level 16, State 1, Procedure sp_fulltext_table, Line 364
Full-text auto propagation is currently enabled for table 'Ticket'.

Obviously a table has an indexing status, i just want to know it show i can display it to the user (i.e. me).

The other available API:

EXECUTE sp_help_fulltext_tables

only returns the tables that are in the catalog, it doesn't return their status.

TABLE_OWNER  TABLE_NAME  FULLTEXT_KEY_INDEX_NAME  FULLTEXT_KEY_COLID  FULLTEXT_INDEX_ACTIVE  FULLTEXT_CATALOG_NAME
===========  ==========  =======================  ==================  =====================   =====================
dbo          DiaryEntry  PK_DiaryEntry_GUID       1                   1                      FrontlineFTCatalog
dbo          Ticket      PK__TICKET_TicketGUID    1                   1                      FrontlineFTCatalog

And i can get the PopulateStatus of an entire catalog:

SELECT FULLTEXTCATALOGPROPERTY('MyCatalog', 'PopulateStatus') AS PopulateStatus

which returns a status for the catalog:

0 = Idle
1 = Full population in progress
2 = Paused
3 = Throttled
4 = Recovering
5 = Shutdown
6 = Incremental population in progress
7 = Building index
8 = Disk is full. Paused.
9 = Change tracking

but not for a table.

SQL Server 2000 SP4

SELECT @@version
Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
    Aug  6 2000 00:57:48
    Copyright (c) 1988-2000 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Regardless of any bug, i want to create UI to easily be able to see its status.

 

 

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