Tips for Troubleshooting Full-Text Index Population Errors

The example below is a typical error you will receive in an email and is also the same error found in the SQL Server error log.

BCTFSQL01, Error: 7683, Severity: 16, State: 1.

Errors were encountered during full-text index population for

            table or indexed view        '[STS_Content_TFS].[dbo].[Docs]',

            database                  'STS_Content_TFS' ( table or indexed view ID  '2089058478', database ID '15' )

When an error occurs during a crawl, the Full-Text Search crawl logging facility creates and maintains a crawl log, which is a plain text file.

Each crawl log corresponds to a particular full-text catalog. By default crawl logs for a given instance, in this case, the first instance, are located in Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder. The crawl log file follows the following naming scheme:

SQLFT<DatabaseID><FullTextCatalogID>.LOG[<n>]

For example, SQLFT0001500005.LOG is the crawl log file for a database with database ID = 15, and full-text catalog ID = 5.

The LOG at the end of the file name indicates that there is only one crawl log file for this database, although in some cases there could be 2, then there would be a 2 at the end of the file name indicating that there are two crawl log files/catalog pair.

So for TFS the crawl log is located at the following location:

\\{server name}\c$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

You need to check the crawl log for the error to get the full-text key value which is a hex value. Part of the error entry in the crawl log will be somethin like this:

Error '0x8004170c: The document format is not recognized by the filter.' occurred during full-text index population for table or indexed view '[STS_Content_TFS].[dbo].[Docs]' (table or indexed view ID '2089058478', database ID '15'),

full-text key value 0x51212D1F9462944E97329D29B35E050F. Attempt will be made to reindex it.

Once you have the full-text key value, connect to the database, in this case ‘STS_Content_TFS’ on {server name} and query the table, in this case it’s ‘docs’ where the id equals the full-text key value.

I.E.
SELECT *
FROM [STS_Content_TFS].[dbo].[Docs]
WHERE ID = 0x51212D1F9462944E97329D29B35E050F

There is a column called DirName,  If you append http://bctfapp01/ to the front of the contents of the DirName column and paste that into IE it will take you out to the website where the document with the error is located.

I.E.
http://bctfapp01/sites/Tax Team Studio/katn/Shared Documents/Projects/DDM

From there the actual issue could be anything, so it’s best to look at who last updated the document and contact them to take care of the issue.

Trying to delete a SQL Instance from polyserve, 3.6 fails with an internal error.

Detail description is:

java.lang.IllegalArgumentException: No value found for sqldataroot
 at com.polyserve.mssql.common.domain.SpackDAO.getParamaterValue(SpackDAO.java:142)
 at com.polyserve.mssql.common.tasks.TaskFactory.removeService(TaskFactory.java:483)
 at com.polyserve.mssql.common.gui.SpackServicePM.doDelete(SpackServicePM.java:320)
 at com.polyserve.mssql.common.gui.SpackServiceEditor.showDeleteProgress(SpackServiceEditor.java:98)
 at com.polyserve.gui.controller.MonitorController$DeleteMonitorAction.actionPerformed(MonitorController.java:110)
 at com.polyserve.mssql.common.gui.SpackServiceController$DeleteAction.actionPerformed(SpackServiceController.java:139)
 at com.polyserve.gui.controller.AbstractController$ProxyVisualAction.actionPerformed(AbstractController.java:198)
 at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
 at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
 at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
 at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
 at javax.swing.AbstractButton.doClick(AbstractButton.java:357)
 at javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1216)
 at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:1257)
 at java.awt.Component.processMouseEvent(Component.java:6038)
 at javax.swing.JComponent.processMouseEvent(JComponent.java:3265)
 at java.awt.Component.processEvent(Component.java:5803)
 at java.awt.Container.processEvent(Container.java:2058)
 at java.awt.Component.dispatchEventImpl(Component.java:4410)
 at java.awt.Container.dispatchEventImpl(Container.java:2116)
 at java.awt.Component.dispatchEvent(Component.java:4240)
 at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322)
 at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986)
 at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916)
 at java.awt.Container.dispatchEventImpl(Container.java:2102)
 at java.awt.Window.dispatchEventImpl(Window.java:2429)
 at java.awt.Component.dispatchEvent(Component.java:4240)
 at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
 at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
 at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
 at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
 at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
 at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
 at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)

Solution provided by technincal support below (it worked)...

• Update the probe parameter by right-clicking on the instance, selecting properties, modify the probe timeout by 1 and hit OK.
• Kill monitor_agent by opening task manager, selecting the processes tab, find monitor_agent.exe and kill the process.  It will automatically restart.

--------------------------------------------------------------------------

SQL Server 2005, plan cache, becoming bloated with excessive amounts of adhoc queries with a usecount of 1, crashing the server.  64 Bit SQL with 32gb of RAM.

Ran some queries to determine which database was causing the most adhoc cache plan bloat, than we set the force auto-parameterization on.

-- Get the size of the Plan Cache (CACHESTORE_SQLCP is non-SP and CACHESTORE_OBJCP is SP)
SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb) ) / (1024.0 * 1024.0) AS 'Plan Cache Size(GB)'
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP'
OR type = 'CACHESTORE_OBJCP'

-- UseCounts and # of plans for Adhoc plans
SELECT usecounts, count(*) as no_of_plans
FROM sys.dm_Exec_Cached_plans
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc'
GROUP BY usecounts
ORDER BY usecounts

Use this DMV query to find the offenders:

-- Find the ad-hoc queries that are bloating the plan cache
SELECT top 1000 *
FROM sys.dm_Exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
--AND size_in_bytes < 200000
ORDER BY size_in_bytes DESC

-- Setting the PARAMETERIZATION option to FORCED
ALTER DATABASE [adtempus_DTSR] SET PARAMETERIZATION FORCED WITH NO_WAIT
GO

-- Setting the PARAMETERIZATION option to SIMPLE (default)
ALTER DATABASE [adtempus_DTSR] SET PARAMETERIZATION SIMPLE WITH NO_WAIT
GO

-- The current setting of this option can be determined by examining
-- the is_parameterization_forced column in the sys.databases catalog view.

When migrating a database, via backup from a sql server 2000 instance to a sql server 2005 instance, the full-text catalogs must be rebuilt.

This is most evident when a full backup fails with the following error:

System.Data.SqlClient.SqlError: The backup of full-text catalog 'DevStudio' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.  (Microsoft.SqlServer.Smo)

To correct this error you can uses pieces of the following script, ensure to change your database, paths and catalogs as appropriate for your environment:

EXEC sp_help_fulltext_catalogs

-- SELECT name FROM sys.fulltext_catalogs;

SELECT FULLTEXTCATALOGPROPERTY('DevStudio','PopulateStatus')

sp_help_fulltext_tables;

sp_help_fulltext_tables @fulltext_catalog_name = 'DevStudio'; -- all the tables linked to that full-text catalog

-- select * from sys.fulltext_indexes

sp_help_fulltext_columns;

sp_help_fulltext_columns @table_name = 'XmlFile' -- returns list of all the columns for a single table

-- select * from sys.fulltext_index_columns

create FULLTEXT CATALOG [CDD]

in PATH N'D:\Shr2k5a\data\FTSearch'

AUTHORIZATION [dbo]

CREATE FULLTEXT INDEX ON XmlFile(Type, Content)

KEY INDEX PK_XmlFile

ON CDD;

EXECUTE sp_fulltext_catalog 'CDD', 'Rebuild';

-- NOTE: The sp_fulltext_database stored procedure with the ENABLE option may be used to rebuild all known full-text catalogs

EXECUTE sp_fulltext_catalog 'CDD_07', 'start_full';

DROP FULLTEXT INDEX ON dbo.XmlFile;

DROP FULLTEXT CATALOG DevStudio;

Upgraded the Cluster to version 3.6 from 3.4, and we lost all network shares on a volume.

These were not shares through the "Polyserve file sharing" components, but standard NT / Windows shares on a Polyserve volume.  [ We do not have the file sharing components, only the database ].

Since there were only 3 shares it was not worth creating a ticket, as investigating root cause on this one-time event (the upgrade) is too much effort.  We just recreated the UNC shares.

I did send a note to the Polyserve HP Support, so they could forward to the engineering team.

Note:  These shares were lost before the upgrade of the dynamic volumes from 3.4 to 3.6, as obviously when you destroy a 3.4 volume and rebuild it as 3.6 you will loose UNC (non polyserve shares).

After upgrading to Polyserve version 3.6, and adding a new server to the cluster that does not have any sql server instances hosted on it, an error is received when rehosting or accessing properties.

The error is:  "Failed to get the matrix topology."  Picture below.

The response from HP Polyserve support is that this is a bug in 3.6, mxDB.  Work around is two uninstall the mxDB module from the server, or setup a sql instance on the server. 

This is not a large issue as the goal of the new server is to host a database on it, so once we setup a sql instance on the machine the error will go away.  A hotfix is in the works for the issue.

The error was concerning because we were not sure if we configured the new server properly, as before we added the new server to the cluster, things worked properly.

Reply from HP Support:

From: Mokhtari, Mostafa [mailto:Mostafa.Mokhtari@hp.com]
Sent: Wednesday, April 23, 2008 11:30 AM
To: Horkay, Robert
Subject: #3601723537

Hi Robert,

I've seen two isolated incidents on SQL Server 2005 where restoring from a SQL Lite Speed backup where the MDF, NDF and LDF file permissions were changed to the user who performed the restore. 

Everthing works fine, but then in the future we decided to move these files, but to our surprise were were unable to move them, giving us an error that the files were in use, read-only or did not have permissions.  We spent considerable time looking for what process had the files in use (Virus Scan, netbackup, SQL ? (process and file explorer from sysinternals), and finally read the error again and decided, maybe the files are read-only !  In the process of checking this, we clicked on the security tab, and the individual who performed the restore was the only account with permissions to the files !

We changed the permissions and copied them fine.

I don't know if this was caused by Polyserve, Lite-Speed or SQL Server; but definetly caused us some frustration, very strange indeed !

While Upgrading Polyserve 3.4 to 3.6, performing a rolling upgrade, on the first server, after starting the services, while performing the license file upgrade, the following error is thrown across all Other servers in the cluster, and they shutdown ! 

So far we are turning off Data Execution Prevention (DEP), for ClusterPulse.exe. 

UPDATE:  Turns out the root cause we started the 3.6 upgrade with the wrong server, you must start with the highest IP Address, we started with the lowest.

To disable DEP for a program or server wide perform the following:

• Right click on My Computer
• Click on Properties
• Click on the Advanced tab
• Click on the Settings button in the Performance section
• Click on the Data Execution Prevention tab

Screen Captures of the Error Message:

Screen Captures of the solution:

Screen Captures of Turning it off:

We've found issues with servers having the IRPStackSize too small, causing numerous entries in the eventlog and shares that do not work.  We also have noticed a correlation between crashes and the entries for IRPStackSize.

Increase this value in the registry by performing the following:

  1. Start the registry editor
  2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
\lanmanserver\parameters.
  3. Double-click IRPStackSize (or if this registry setting doesn't exist,
create it of type DWORD and ensure the case is correct).
  4. Change the base to decimal, set the value to 15 and click OK.  If a
value is already present, add 3 to that number, and set it to the new
number.
  5. Reboot the server

Polyserve clusters SQL Server instances, and can then rehost them or fail them over to other servers.

One of the issues we've run into with Polyserve is when we move a sql server instance from one machine to another where the binaries change location from mssql.a to mssql.b.

SQL Agent (SQLAgent.exe, SQLAgent), utilizes a system table in msdb for starting and executing jobs, called syssubsystems;

Execute: select * from msdb.dbo.syssubsystems and you will see each sub-system of sql agent and the path to the binaries, if a sql server instance is moved through some technology like Polyserve or through manually moving a msdb database from one server to another and the path to the binaries change, the system tables will need to be updated to the new path.

We've experienced this on Polyserve and when we moved a sql server instance from one server to another and copied msdb.

very easy to fix, but frustrating, below is  a snippet which can be run to correct the issue:

declare @ret sysname

exec master..xp_instance_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Setup','SQLBinRoot',@ret OUTPUT

update sub

set subsystem_dll=@ret+substring(subsystem_dll,charindex('\binn\',subsystem_dll)+5,30)

from msdb..syssubsystems sub

where charindex('\binn\',subsystem_dll)>0

declare @service sysname

select @service = case when charindex('\',@@servername)>0

then N'SQLAgent$'+@@servicename

else N'SQLSERVERAGENT' end

create table #stat(status sysname)

insert #stat

exec master..xp_servicecontrol N'QUERYSTATE', @service

if exists(select * from #stat where status='Running.')

begin

exec master..xp_servicecontrol N'STOP', @service

end

if object_id('tempdb..#stat') is not null

drop table #stat

Recently we began deploying cumulative update 6 for SQL Server 2005.  This brings the version number to 9.00.3228.

So far we've deployed both the 32 bit and 64 bit version.  Deployed to Polyserve as well.  Have about 8 installations so far.

Nothing special to note, seems we have not had the same issues we had installing Cumulative Update 5.

We still have problems with adhoc query plans bloating the cache and the tokenpermuserstore.

Not knowing if Microsoft will release SQL Server 2005 Service Pack 3 by October 2008, which is when we lock our environment down until May 1 the following year...we will begin deploying this update to all SQL Server 2005 Installations.

KB946608

Hot Fix for Microsoft Knowledge Base article number(s) 942907, 942908, 943526, 944358, 944929, 945442, 945443, 945640, 945641, 945916, 946608, 946793, 947007, 947008, 947179, 947196, 947197, 947204, 947414, 947462, 947463, 947975, 948033, 948248, 948367, 948445, 948490, 948508, 948521, 948523, 948525, 948578, 948582, 948622, 948628, 948754, 948920, 949097, 949105, 949108, 949115, 949116, 949117, 949118, 949119, 949120, 949121, 949200

Knowledge Base Article
======================
To view the contents of the knowledge base article for this update, visit
http://support.microsoft.com/?kbid=942907, 942908, 943526, 944358, 944929, 945442, 945443, 945640, 945641, 945916, 946608, 946793, 947007, 947008, 947179, 947196, 947197, 947204, 947414, 947462, 947463, 947975, 948033, 948248, 948367, 948445, 948490, 948508, 948521, 948523, 948525, 948578, 948582, 948622, 948628, 948754, 948920, 949097, 949105, 949108, 949115, 949116, 949117, 949118, 949119, 949120, 949121, 949200 directly, or
visit http://support.microsoft.com and query the Knowledge base
for the product and the KB number of this update.

Polyserve upgrade to 3.6 for our development environment is completed.

The directions are pretty clear, but could use some help.

We performed a rolling upgrade.  Nothing major found.

The most difficult part is the completion steps of growing the membership partitions and rebuilding volumes to be a 3.6 file system....this requires some "swing" luns from the SAN so data can be moved around.