February 11, 2011 at 11:37 am
We currently have a table which has about 2483900 records in it already and one of the fields is a varchar(max) type. Doing searches keeps bombing so someone suggested full-text indexing. I created it but when I do a search no results return.
Here is what I have done.
--STEP 1 - create filegroup
USE dbname;
GO
ALTER DATABASE dbname
ADD FILEGROUP FG_FULLINDEX
GO
--STEP 2 -- Add file to filegroup
ALTER DATABASE dbname
ADD FILE
(
NAME = FG_FULLINDEX,
FILENAME = 'C:\Data\fg_fullindex.mdf',
SIZE = 100MB,
MAXSIZE = 200MB,
FILEGROWTH = 20MB
) TO FILEGROUP FG_FULLINDEX;
GO
--STEP 3 -- Add catalog
CREATE FULLTEXT CATALOG FT_USERS ON FILEGROUP FG_FULLINDEX AS DEFAULT;
--STEP 4 -- see if catalog was created
SELECT fulltext_catalog_id, name FROM sys.fulltext_catalogs
--STEP 5 -- create index
CREATE FULLTEXT INDEX ON tablename (fieldnamethathasdatatosearch) KEY INDEX PK_tablename ON FT_USERS
--STEP 6 -- see if full-text index is created SELECT t.name AS TableName, c.name AS FTCatalogName FROM sys.tables t JOIN sys.fulltext_indexes i
ON t.object_id = i.object_id
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id
When I do the following query it returns 0 rows:
select * from tablename where CONTAINS(fieldnamethathasdatatosearch, 'BRENT*')
February 11, 2011 at 1:26 pm
Has the full text index been populated? When you create a full text index you have three options to track changes, Automatic, Manual, or Do not track changes. I use Automatic which should cause the index to be populated during creation.
CREATE FULLTEXT INDEX ON [dbo].[TableName](
[fullTextSearchDataColumnName])
KEY INDEX [PK_TableName] ON [MySearchFullTextCatalog]
WITH CHANGE_TRACKING AUTO
February 11, 2011 at 1:30 pm
So I am guessing since I didn't do the same as you it's probably not populated. Should I delete and recreate with your syntax or is there a way to manually populate?
February 11, 2011 at 1:42 pm
Yeah, I think that would be the easiest. Just drop the index and recreate it with my example. This will also ensure that when the data in the table is updated, the full text index is also updated immediately at the same time. Otherwise, you would run the risk of someone updating a name in the table, then doing a search, and still not getting any results.
February 12, 2011 at 10:25 am
Here is what I did.
In Microsoft SQL Server Management Studio I went to db/Storage/Full Text Catalogs and clicked the 'Delete' to remove the index.
Then I did the following:
CREATE FULLTEXT CATALOG FT_USERS ON FILEGROUP FG_FULLINDEX AS DEFAULT;
CREATE FULLTEXT INDEX ON tablename (fieldnamethathasdatatosearch) KEY INDEX PK_tablename ON FT_USERS CHANGE_TRACKING AUTO
I try to run my query again and still no data returned.
So I tried doing:
exec sp_fulltext_catalog 'FT_USERS','start_full'
and got the following warning.
Warning: Request to start a full-text index population on table or indexed view 'dbo.tablename' is ignored because a population is currently active for this table or indexed view.
Does something have to trigger it?
February 14, 2011 at 10:19 am
Is there a way to tell if it's setup properly? According to these it doesn't look like it's populating properly.
select fulltextcatalogproperty('FT_USERS', 'PopulateStatus')
--RESULT: 1
select fulltextcatalogproperty('FT_USERS', 'ItemCount')
--RESULT: 0
select fulltextcatalogproperty('FT_USERS', 'LogSize')
--RESULT: 0
select fulltextcatalogproperty('FT_USERS', 'IndexSize')
--RESULT: 0
February 14, 2011 at 10:37 am
I was just replying to your previous post saying that it would probably take a while to process your 2.5 million rows. Perhaps 30 minutes or more (I don't know how much data is in the 2.5 million rows).
Reviewing your query results, however, the first query indicates that the index population is still in progress. So has it been running all weekend? What is the server's CPU and disk usage like?
February 15, 2011 at 8:19 am
I don't think anything related to the index is running. I have a process run twice a day that does inserts/updates.
How does it get activated? Is there another way to tell if it's working properly or setup properly?
Here are the stats:
Disk:
136GB total
4.80 GB free space
Physical Memory (K)
Total: 12581572
Available: 6669532
System:
Microsoft Windows Server 2003 R2
Standard x64 Edition SP2
Processor:
Intel Xeon E5320 @ 1.86Ghz
February 15, 2011 at 11:00 am
So in your SQL, you defined your own file group for the full text catalog. I don't know where that exists physically on your disk, but I used SSMS to create the catalog and index. In there, when creating the catalog, you are asked for four things, a name, location, filegroup, and owner. Since you did not specify a location, it will use the default, which is the install directory which by default is something like C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL. I'm not sure where inside there the files will exist, but for me, when I defined the location, it places three files and a subfolder:
MsSearchCatalogDir
CatalogStatus.dat
CatalogStatus.idx
SQL.HDR
Inside the MsSearchCatalogDir directory, there are 104 files (could be more or less depending on your data I'm sure) that basically make up the index and data of the full text index. These files have a hex filename with an extension of either .ci, .dir, or .wid. You could search your C:\Program Files\Microsoft SQL Server\ folder for those extensions to see if the files exist.
Other than that, looking over the documentation it appears that the default change tracking for a full text index is AUTO, so your original SQL should be OK. Can you run the following queries:
select SERVERPROPERTY('IsFullTextInstalled')
--1
select DATABASEPROPERTY('DBName', 'IsFullTextEnabled')
--1
select OBJECTPROPERTYEX(OBJECT_ID(N'DBName.dbo.TableName'), 'TableFullTextBackgroundUpdateIndexOn')
--1
select OBJECTPROPERTYEX(OBJECT_ID(N'DBName.dbo.TableName'), 'TableFulltextPopulateStatus')
--0
select OBJECTPROPERTYEX(OBJECT_ID(N'DBName.dbo.TableName'), 'TableFulltextPendingChanges')
--0
select OBJECTPROPERTYEX(OBJECT_ID(N'DBName.dbo.TableName'), 'TableFulltextKeyColumn')
--1
select OBJECTPROPERTYEX(OBJECT_ID(N'DBName.dbo.TableName'), 'TableFullTextChangeTrackingOn')
--1
Finally, in SQL Server 2005, the full text indexing is a completely separate service. Can you log into the server and under Control Panel -> Administrative Tool -> Services, see if you have the service "SQL Server FullText Search (MSSQLSERVER)" installed and running.
February 15, 2011 at 2:32 pm
In C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\FT_USERS I have 1 file and 1 folder and within the 1 folder are only a handful of files all with date modified on 2/12/2011
MssearchCatalogDir
-CiAB0001.000
-CiAB0001.001
-CiAB0001.002
-CiAB0002.000
-CiAB0002.001
-CiAB0002.002
-CiAD0001.000
-CiAD0001.001
-CiAD0001.002
-CiPT0000.000
-CiPT0000.001
-CiPT0000.002
-INDEX.000
-INDEX.001
-INDEX.002
-SETTINGS.DIA
-Used0000.000
-USed0000.001
-Used0000.002
SQL.HDR
Of the commands you had I only have 1 that returned something different:
select OBJECTPROPERTYEX(OBJECT_ID(N'DBName.dbo.TableName'), 'TableFulltextPopulateStatus')
--1
As for Services, here is what my says:
Name: SQL Server FullText Search(MSSQLSERVER)
Status: Started
So it appears something is there but it's like it's not updating or populating automatically.
February 15, 2011 at 2:48 pm
What is the size of all your files and directory in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\FT_USERS?
A TableFulltextPopulateStatus of 1 means that it is still populating.
Can you check the PhysicalDisk->% Idle Time performance counter in Perfmon of the server's hard drives. (Open Perfmon by Start->Run->"perfmon". Then click on the Plus toolbar icon to add a counter. In the Performance object drop down list, select "PhysicalDisk". Then select the "% Idle Time" counter from the list below that. In the "instances" list, select your C: drive, and whatever physical drive your database is on. Then click the "Add" button.)
Since the performance counter is "% Idle Time", a disk that's not doing anything will be at 100% idle and the line in the graph will be at the top.
If your disk is 100% idle, then yeah, something is wrong.
Can you reboot the entire server?
Can you try creating a full text index on a smaller table?
February 15, 2011 at 3:05 pm
Size of C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\FT_USERS? is: 288 KB
% Idle Time: around 97
Should I delete the fulltext index and try it again?
Is the best way to remove it by finding database, Stoarage > Full Text Catalogs > FT_USERS and right click and select delete? I think I did this before and maybe it didn't clean it up completely?
February 15, 2011 at 3:23 pm
Oh, one other thing I was going to have you look at is the server's Event Viewer (Control Panel->Administrative Tools->Event Viewer). Look for anything in the Application and System logs related to SQL Server and full text indexing. See if you can find any errors or warnings. I'm not sure what error/warning messages might be in there, if any, so you'll have to dig.
Other than that, yeah, I'm running out of ideas for you, sorry. You can try deleting the catalog via SSMS. It's under DatabaseName->Storage->Full Text Catalogs.
I'd try to create one on a really small table. Then reboot the server if it still doesn't work.
I'm not sure if you have any special permissions, user roles, or schemas setup. I'm not familiar with how those would effect the full text indexing service.
February 15, 2011 at 3:26 pm
I removed the index and recreated.
Looking at files I noticed in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG a file SQLFT0000900006.LOG which is 3,851 KB.
Here are the last few logs but I see a bunch of them repeating:
2011-02-15 16:07:44.59 spid21s Error '0x80004005' occurred during full-text index population for table or indexed view '[MyDatabaseName].[dbo].[MyTableName]' (table or indexed view ID '366624349', database ID '9'), full-text key value 0x00000E43. Attempt will be made to reindex it.
2011-02-15 16:07:44.59 spid21s The component 'sqlfth90.dll' reported error while indexing. Component path 'C:\Program Files\Microsoft SQL Server\90\COM\sqlfth90.dll'.
2011-02-15 16:08:54.57 spid17s Error '0x80004005' occurred during full-text index population for table or indexed view '[MyDatabaseName].[dbo].[MyTableName]' (table or indexed view ID '366624349', database ID '9'), full-text key value 0x00000E44. Attempt will be made to reindex it.
2011-02-15 16:08:54.57 spid17s The component 'sqlfth90.dll' reported error while indexing. Component path 'C:\Program Files\Microsoft SQL Server\90\COM\sqlfth90.dll'.
Any thoughts on why I would get these errors?
February 15, 2011 at 3:37 pm
It appears to be working because my log file is up to 83,251 KB in like 20 minutes since I created it and all that is in it is the errors reported earlier so that appears to be the issue.
2011-02-15 16:33:47.50 spid23s Error '0x80043651: msftesql should reprocess this document in an isolated fashion to confirm the error.' occurred during full-text index population for table or indexed view '[MyDatabaseName].[dbo].[MyTableName]' (table or indexed view ID '366624349', database ID '9'), full-text key value 0x00003E8E. Attempt will be made to reindex it.
2011-02-15 16:33:47.50 spid23s The component 'MSFTE.DLL' reported error while indexing. Component path 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\MSFTE.DLL'.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply