Jimmy Harper's Operations Manager Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of Use

SQL Server Full Text Search Service Monitor

SQL Server Full Text Search Service Monitor

  • Comments 9
  • Likes

This issue is described in the SQL Server Management Pack Guide, but I wanted to blog it since I’ve seen a couple customers hit it.

In the current version of the SQL Server Management Pack (version 6.0.6559.0), we have a monitor for the SQL Server Full Text Search Service which is targeted at the SQL 2005/2008 DB Engine classes. 

image

 

The problem is, this is an optional component in SQL Server and is not always installed.  So, for servers where this service is not installed, we will see a lot of the following alerts:

Alert Name:

Service Check Probe Module Failed Execution

or

Service Check Data Source Module Failed Execution

Alert Description:

Error getting state of service Error: 0x8007007b Details: The filename, directory name, or volume label syntax is incorrect. One or more workflows were affected by this. Workflow name: Microsoft.SQLServer.2005.DBEngine.FullTextSearchServiceMonitor Instance name: MSSQLSERVER Instance ID: {625091EA-A1D9-1857-802C-0D908C93A5BB} Management group: jimmyh_mg1

 

image

 

 

To fix this, all we need to do is disable this monitor on any SQL Server that does not have the Full Text Search Service installed.  The easiest way to do this is to create a group for all of the SQL Instances that do not have the service installed.  The Full Text Search Service name is one of the discovered properties for the DB Engine class and will be blank if the service is not installed:

 

image

 

 

To create a group of SQL instances that do not have it installed, we can just use the criteria “Does not match regular expression . (dot)”, like this:

 

image 

 

 

Then, just set an “Enabled=False” override on the monitor, targeted at this group:

 

image

 

Repeat the same steps to create the group and override for SQL 2008 DB Engines.

One more thing that you’ll want to do with this monitor is set the “Alert only if startup type is automatic” override to False for clustered SQL Instances…..since the service will always be in a Manual startup mode.

To do this, I create a group of Cluster SQL Instances where Full Text Search Service IS Installed:

 

image

 

 

And target the override at this group:

 

image

 

Again, repeat for SQL 2008 DB Engines.

Attached is a sample MP that contains the above groups and overrides for SQL 2005 DB Engines.

 

UPDATED:

I've removed the original attachment and attached a .zip file that contains these MPs for both SQL 2005 and SQL 2008.

Attachment: Full.Text.Search.Service.Monitor.Groups.and.Overrides.zip
Comments
  • Hi!, Nice post. Do you have this MP for SQL 2008?

    Regards!

  • I do...just need to fix some things in it that I didn't get around to fixing when I blogged this....I'll have it up in a bit.

  • Thanks so much for taking the time to post this. I bet most people are like me - it occurred to me first to google the alert text rather than look in the m.p. guide, based on a history of having Microsoft documentation being totally useless for troubleshooting...

  • Note that SQL Express does not include the SQL Server Full Text Search components, so all of these editions will cause Event ID 11771.

  • Thanks for taking the time to post this..i needed to know how fix it

  • Microsoft needs to actually fix this.  It is ridiculous to have an alert generated on a system if the component or process isn't even installed.

  • Great writeup, and great follow-through providing the MPs as well. Thanks much!

  • Great writeup!  I imported the MP and everything looks great, except that I see the folders in my monitoring view with no ability to view or modify the properties.  I'm a SCOM newb ... any help would be appreciated.

  • Hi

    I am using MSSQL Server 2005 with the Full Text Search.

    Query with Contains and like not give the same results

    My Query is

    Select * from Searchtable where (ipccode like '%A61K 36/00%' or ipccode like '%A61P 17/00%') and (ipccode like '%C13K 13/00%' and ipccode like '%A61P 1/00%')

    Its gives the 78 results and

    Select * from Searchtable Where ( Contains( IPCCode ,'("A61K 36/00" or "A61P 17/00") and ("C13K 13/00" and "A61P 1/00")') )

    Its gives the 355 results

    both are same query first with Like and second with Contains, why results come with differents.

    Any help to find out the problem

    Thanks and Regards

    Manohar

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment