Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list
here.
Goto
Definition or
MetaData sys.sp_fulltext_service(
nvarchar @action,
sql_variant @value)
create proc sys.sp_fulltext_service @action nvarchar(100) = NULL, @value sql_variant = NULL as set nocount on -- sp_fulltext_service will run under read committed isolation level -- set transaction isolation level READ COMMITTED -- CHECK PERMISSIONS (must be serveradmin) -- if (is_srvrolemember('serveradmin') = 0) begin raiserror(15247,-1,-1) return 1 end -- DISALLOW USER TRANSACTION -- set implicit_transactions off if @@trancount > 0 begin raiserror(15002,-1,-1,'sys.sp_fulltext_service') return 1 end -- initialize full-text configuration table -- declare @fulltext_configuration_table TABLE ( Name nvarchar(100) NOT NULL UNIQUE, IsDeprecated smallint default 0, IsVisible smallint default 0, FNC_Name nvarchar(200), HasValue smallint default 0, Type nvarchar(30) default NULL, NeedRangeCheck smallint default NULL, minvalue bigint default NULL, maxvalue bigint default NULL, dflt_longlong bigint default NULL, dflt_str nvarchar(1024) default NULL) -- for resource_usage, we set both index and query performance level to the value. So when we read value we just return -- index performance level. Index and query performance level are suppose to be same. However, user can directly set -- index and query performance level and they can be different. Resouce usage is just a setting for backward compatibility. -- name depre, visi,FNC_Name hasVal, type, rangechk, min, max, dfltl, dftlstr insert into @fulltext_configuration_table values ('resource_usage', 1, 1, 'FTE_IndexingPerformanceLevel', 1, 'int', 1, 0, 0, NULL, NULL) insert into @fulltext_configuration_table values ('clean_up', 1, 1, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL) insert into @fulltext_configuration_table values ('connect_timeout', 1, 1, NULL, 1, 'int', 1, 0, 0, NULL, NULL) insert into @fulltext_configuration_table values ('data_timeout', 1, 1, NULL, 1, 'int', 1, 0, 0, NULL, NULL) insert into @fulltext_configuration_table values ('update_languages', 0, 1, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL) insert into @fulltext_configuration_table values ('restart_all_fdhosts', 0, 0, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL) insert into @fulltext_configuration_table values ('pause_indexing', 0, 1, 'pause_indexing', 1, 'int', 1, 0, 1, NULL, NULL) -- The following are config values insert into @fulltext_configuration_table values ('load_os_resources', 0, 1, 'FTE_LoadOSResources', 1, 'int', 1, 0, 1, 0, NULL) insert into @fulltext_configuration_table values ('verify_signature', 0, 1, 'FTE_VerifySignature', 1, 'int', 1, 0, 1, 1, NULL) insert into @fulltext_configuration_table values ('aggressive_break', 0, 0, 'FTE_AggressiveBreak', 1, 'int', 1, 0, 1, 1, NULL) insert into @fulltext_configuration_table values ('ism_size', 0, 0, 'IFTS_ISMSize', 1, 'int', 1, 1, 16, 0, NULL) insert into @fulltext_configuration_table values ('batch_size', 0, 0, 'IFTS_BatchSize', 1, 'int', 1, 100, 10000, 0, NULL) insert into @fulltext_configuration_table values ('outstanding_batches', 0, 0, 'IFTS_OutstandingBatches', 1, 'int', 1, 1, 100, 5, NULL) insert into @fulltext_configuration_table values ('outstanding_isms', 0, 0, 'IFTS_OutstandingISMs', 1, 'int', 1, 1, 100, 5, NULL) insert into @fulltext_configuration_table values ('auto_crawl_end_fragments_merge', 0, 0, 'IFTS_AutoCrawlEndMergeFragments', 1, 'int', 1, 2, 50, 5, NULL) insert into @fulltext_configuration_table values ('fdhost_tracing', 0, 0, 'IFTS_FDHostTracing', 1, 'int', 1, 0, 1, 0, NULL) insert into @fulltext_configuration_table values ('fdhost_tracing_numberoflogs',0, 0, 'FTE_RetailTracingNumberOfLogs',1, 'int', 1, 1, 500, 10, NULL) insert into @fulltext_configuration_table values ('fdhost_tracing_tag', 0, 0, 'IFTS_FDHostTracingTag', 1, 'nvarchar', NULL, NULL, NULL, NULL, N'') insert into @fulltext_configuration_table values ('fdhost_dump', 0, 0, 'IFTS_FDHostDump', 1, 'int', 1, 0, 1, 1, NULL) insert into @fulltext_configuration_table values ('processor_affinity', 0, 0, 'FTE_ProcessorAffinity', 1, 'int', NULL, NULL, NULL, 0, NULL) insert into @fulltext_configuration_table values ('crawl_ranges', 0, 0, 'IFTS_NumRanges', 1, 'int', 1, 0, 256, 0, NULL) insert into @fulltext_configuration_table values ('master_merge_dop', 0, 0, 'IFTS_PmmDop', 1, 'int', 1, 0, 256, 0, NULL) insert into @fulltext_configuration_table values ('ft_timeout', 0, 0, 'IFTS_FTTimeout', 1, 'int', 1, 10000, 6000000,60000, NULL) insert into @fulltext_configuration_table values ('max_singledoc_retries', 0, 0, 'IFTS_MaxSingleDocRetries', 1, 'int', 1, 0, 10, 1, NULL) insert into @fulltext_configuration_table values ('upgrade_option', 0, 1, 'IFTS_CatalogUpgradeOptions', 1, 'int', 1, 0, 2, 2, NULL) insert into @fulltext_configuration_table values ('fdhost_dump_flags', 0, 0, 'FTE_SQLDumperFlags', 1, 'nvarchar', NULL, NULL, NULL, NULL, N'0x820') IF SERVERPROPERTY('IsNonGolden') IS NOT NULL begin insert into @fulltext_configuration_table values ('mm_partition_count', 0, 0, 'FTE_SQLMMPartitionCount', 1, 'int', 1, 0, 1000, 1000, NULL) insert into @fulltext_configuration_table values ('merge_range_size_in_bytes', 0, 0, 'FTE_SQLMergeRangeSizeInBytes', 1, 'int', 1, 0, 100 * 1024 * 1024, 100 * 1024 * 1024, NULL) insert into @fulltext_configuration_table values ('costing_factor', 0, 0, 'IFTS_Costingfactor', 1, 'int', 1, 1, 1000000, 1000, NULL) end -- if action is null, we print out all configuration options that are available to user -- if @action is null begin select Name as 'configuration name', CASE IsDeprecated WHEN 1 THEN 'Yes' ELSE 'No' end as 'is deprecated', Type as 'configuration value type', minvalue as 'minimum', maxvalue as 'maximum' from @fulltext_configuration_table where IsVisible = 1 order by Name return 0 end -- utility: list all sp_fullext_service options if @action = 'show_all' begin select Name as 'configuration name', CASE IsDeprecated WHEN 1 THEN 'Yes' ELSE 'No' end as 'is deprecated', Type as 'configuration value type', minvalue as 'minimum', maxvalue as 'maximum' from @fulltext_configuration_table order by Name return 0 end declare @Name nvarchar(100) declare @IsDeprecated smallint declare @FNC_Name nvarchar(200) declare @HasValue smallint declare @Type nvarchar(30) declare @NeedRangeCheck smallint declare @minvalue bigint declare @maxvalue bigint declare @dfltlonglong bigint declare @dfltstr nvarchar(1024) declare @sval nvarchar(1024) -- find configuration option from the table -- select @Name = Name, @IsDeprecated = IsDeprecated, @FNC_Name = FNC_Name, @HasValue = HasValue, @Type = Type, @NeedRangeCheck = NeedRangeCheck, @minvalue = minvalue, @maxvalue = maxvalue, @dfltlonglong = dflt_longlong, @dfltstr = dflt_str from @fulltext_configuration_table where Name = @action -- Check if configuration option is exposed to user or Yukon Style options for mssearch internal use (no longer supported) if (@Name is null) begin -- Yukon Style options for mssearch internal use (MSSearch only) options are not supported anymore -- It has been decided that we throw an error for most of the MSSearch only options (some options will still be supported) -- These options are not documented and has not been advertised to general public or CSS. -- The other option is to make it a no-op for options we no longer support but it was decided (Jingweil and deepakp) -- that we throw an error for the reasons explained above. -- raiserror(15600,-1,-1,'sys.sp_fulltext_service') return 1 end -- if value is null, we print the option value -- if (@value is null) begin -- if the option doesn't have value, we will just run the command if (@HasValue = 0) begin -- If it is deprecated, we will not do anything -- if (@IsDeprecated = 1) begin -- 21 logs the usage of the deprecated action and increments -- the appropriate perf counter for this deprecated feature -- DBCC CALLFULLTEXT(21, @Name) return 0 end -- DBCC CALLFULLTEXT (18 ) -- if @action = 'update_languages' begin DBCC CALLFULLTEXT (18 ) -- end else if @action = 'restart_all_fdhosts' begin DBCC CALLFULLTEXT (22 ) end else begin -- current only update_languages is non-value option raiserror(15600,-1,-1,'sys.sp_fulltext_service') return 1 end end else begin -- If it is deprecated, we will print 0 as value -- if (@IsDeprecated = 1) begin select @Name as 'Configuration Name', 0 as 'Configuration Value' -- 21 logs the usage of the deprecated action and increments -- the appropriate perf counter for this deprecated feature -- DBCC CALLFULLTEXT(21, @Name) return 0 end -- print the configuration name and value DBCC CALLFULLTEXT(2, @FNC_Name, @Name) end if @@error <> 0 return 1 end else begin if (@HasValue = 0) begin -- try to set an option which don't take a value -- -- invalid parameter, raise error here -- raiserror(15600,-1,-1,'sys.sp_fulltext_service') return 1 end else begin -- If it is deprecated, we will not do anything -- if (@IsDeprecated = 1) begin -- 21 logs the usage of the deprecated action and increments -- the appropriate perf counter for this deprecated feature -- DBCC CALLFULLTEXT(21, @Name) return 0 end select @sval = convert(nvarchar(1024), @value) if @@error <> 0 return 1 if (@Type = 'int') begin -- convert to int value first -- declare @i bigint if ltrim(rtrim(lower(@sval))) = N'default' select @i = @dfltlonglong else begin select @i = convert(bigint, @sval) if @@error <> 0 return 1 if @i = -1 select @i = @dfltlonglong end -- check if the value is in the range -- if ((@NeedRangeCheck = 1) and ( @i is null or (not (@i between @minvalue and @maxvalue) and @i <> @dfltlonglong))) begin raiserror(15600,-1,-1,'sys.sp_fulltext_service') return 1 end select @value = @i end else begin if ltrim(rtrim(lower(@sval))) = N'default' select @value = @dfltstr else select @value = @sval end if @action = 'pause_indexing' begin DBCC CALLFULLTEXT ( 19, @value ) -- end else begin DBCC CALLFULLTEXT ( 1, @FNC_Name, @value) -- Set config value end if @@error <> 0 return 1 end end -- SUCCESS -- return 0 -- sp_fulltext_service
