Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Wednesday, 30 April 2008

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.

Wednesday, 30 April 2008 13:44:30 (Central Standard Time, UTC-06:00) | Comments [0] |  Full-Text#
Tuesday, 29 April 2008

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.

Tuesday, 29 April 2008 12:17:55 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Friday, 25 April 2008

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:

/*

Create a Full-Text Catalog and Index

*/

/**************** Return info about Full-Text Catalog and Index *********************/

-- Obtain a list of all the full-text catalogs linked to the current database by running this query:

EXEC sp_help_fulltext_catalogs

-- SELECT name FROM sys.fulltext_catalogs;

-- Returns information about full-text catalog properties

SELECT FULLTEXTCATALOGPROPERTY('DevStudio','PopulateStatus')

-- Obtain a list of all the tables in the database that have been enabled for full-text processing:

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

-- Obtain a list of all the columns in the database that have been enabled for full-text processing:

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 a Full-Text Catalog and Index *********************/

-- CREATE FULLTEXT CATALOG DevStudio;

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;

 

/**************** Rebuild and Populate Full-Text Catalog *******************/

-- Rebuild, but do not repopulate, the full-text catalog:

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

-- Start a full population of the full-text catalog

EXECUTE sp_fulltext_catalog 'CDD_07', 'start_full';

 

/**************** Drop a Full-Text Index and Catalog **********************/

-- Unregister a table for full-text processing:

DROP FULLTEXT INDEX ON dbo.XmlFile;

-- Drop the full-text catalog from the file system and its metadata from the catalog views:

DROP FULLTEXT CATALOG DevStudio;

 

Friday, 25 April 2008 12:00:46 (Central Standard Time, UTC-06:00) | Comments [0] |  Full-Text#
Thursday, 24 April 2008

SQL Server 2005, Cumulative Update 7.

We downloaded and applied for testing, all tested well.

We tried to implement, and ran into a problem with a vendor install for Symantec End Point reporting database.

We could not figure out the issue, the vendor recommended reverting back to SQL Server 2005, without CU7.  Installation completed succesfully.

We never fullly figured out what was in the vendor script that caused it to fail under CU7 and work in CU6, but obviously something was different.

Based on this "unknown" factor we have not standardized on CU7, in the past none of the CU's have ever broken the functionality of an application, but something in CU7 sure does with Symantec End Point reporting.

Thursday, 24 April 2008 11:30:24 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#

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).

Thursday, 24 April 2008 09:12:34 (Central Standard Time, UTC-06:00) | Comments [0] | Polyserve#
Wednesday, 23 April 2008

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,

What you are experiencing with re-hosting is a bug with MxDB and the fix is going to be in the next version.
Problem is not with the NIC.
The issue occurs when mxdb is installed on a node that does not have any instances installed. So once the instances are on the new node it should go away
 
Two workaround
Uninstall MxDB from the node or install the instance on it.
 
Let me know if you have any question,
 
Regards,
Mostafa Mokhtari
HEWLETT-PACKARD COMPANY
High Availability Team
Monday-Friday 8-4pm PST
Wednesday, 23 April 2008 09:06:17 (Central Standard Time, UTC-06:00) | Comments [0] | Polyserve#
Tuesday, 22 April 2008

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 !

Tuesday, 22 April 2008 13:47:24 (Central Standard Time, UTC-06:00) | Comments [0] | Polyserve | SQL Server#
Monday, 21 April 2008

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:

 

Monday, 21 April 2008 16:00:49 (Central Standard Time, UTC-06:00) | Comments [0] | General Technology | Polyserve#
Monday, 14 April 2008

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

 

 

Monday, 14 April 2008 12:06:17 (Central Standard Time, UTC-06:00) | Comments [0] | Polyserve#
Friday, 04 April 2008

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)...


No value found for sqldataroot (3.4 upgrade to 3.6)
This error indicates that monitor_agent is still using 3.4 parameters, and therefore needs to be updated using one or both of these methods:
 
  • 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.

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


From: Mokhtari, Mostafa [mailto:Mostafa.Mokhtari@hp.com]
Sent: Friday, April 04, 2008 11:29 AM
To: Horkay, Robert
Subject: RE: #3601519915

You would want to do this on any instance that's having the problem.  If changing the instance properties doesn't update the monitor and you decide to kill monitor agent, then killing monitor agent would only need to be performed once per node.

 

Regards,

Mostafa Mokhtari

HEWLETT-PACKARD COMPANY

High Availability Team

(719) 592-6700 ext. 65209

Monday-Friday 8-4pm PST

 


From: Horkay, Robert [mailto:RHorkay@....com]
Sent: Friday, April 04, 2008 9:19 AM
To: Horkay, Robert; Mokhtari, Mostafa
Subject: RE: #3601519915

 

Ok,

 

That worked !

 

Do we need to do this for every instance ?  or would it happen automatically as a box was restarted (as that would cause the monitor_agent to restart)....as we have yet to reboot every box after the 3.6 upgrade...

 

bob

 


From: Horkay, Robert
Sent: Friday, April 04, 2008 11:13 AM
To: 'Mokhtari, Mostafa'
Subject: RE: #3601519915

Yes that is correct.

 

I will try that.

 

On a side note, no where in the documentaiton on 3.4 to 3.6 upgrade did it mention doing this...?

 

let me see if it works...

 

bob

 


From: Mokhtari, Mostafa [mailto:Mostafa.Mokhtari@hp.com]
Sent: Friday, April 04, 2008 11:11 AM
To: Horkay, Robert
Subject: RE: #3601519915

 

Was this an upgrade from 3.4?  If so,

 

No value found for sqldataroot (3.4 upgrade to 3.6)

This error indicates that monitor_agent is still using 3.4 parameters, and therefore needs to be updated using one or both of these methods:

 

  • 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.

 

Regards,

Mostafa Mokhtari

HEWLETT-PACKARD COMPANY

High Availability Team

(719) 592-6700 ext. 65209

Monday-Friday 8-4pm PST

.

 

 

_____________________________________________
From: Mokhtari, Mostafa
Sent: Friday, April 04, 2008 8:57 AM
To: 'Horkay, Robert'
Subject: #3601519915

 

Hi Robert,

I just picked up your case. Is this x64 OS or x86? What is the version of your SQL?

 

Thanks,

Mostafa Mokhtari

HEWLETT-PACKARD COMPANY

High Availability Team

(719) 592-6700 ext. 65209

Monday-Friday 8-4pm PST

 

 

 

Friday, 04 April 2008 09:50:48 (Central Standard Time, UTC-06:00) | Comments [0] | Polyserve#
Wednesday, 02 April 2008

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.

 

 

Wednesday, 02 April 2008 09:45:10 (Central Standard Time, UTC-06:00) | Comments [0] | Polyserve#
Tuesday, 01 April 2008

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.

Tuesday, 01 April 2008 10:50:56 (Central Standard Time, UTC-06:00) | Comments [0] | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Server Security, not where it n...
DTA - Failed to initialize MSDB dat...
Check the Uptime of a Windows Serve...
Recent Posts
Archive
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll