|
|
You're in the
sql server full text search
group:'update index in background' not running.
sql server full text search:
this is a new windows 2003 server/sql server 2000 set up (2-3 days old).
full text is set up and working and definetely added data, andi have update index in background set. It seems to have worked for a while, then stopped. The application event log records events for Microsoft Search as 'Search service has started', 'Search service has loaded project ...' and 'indexer started successfully for project..' from yesterday afternoon, about the time that it stopped adding articles. There is only one catalog and one table with two fields that are indexed. since there is data in there it seems to have stopped in the last day or so. the system is lightly loaded, nothign else going on, and i can see mssearch.exe in the task list. where can i look to see why its not loading? thanks, john
John,
First you should review the server's "Microsoft Search" and MssCi source events (informational, warnings & errors), as it is only in the Application event log that SQL Server 2000 FT Indexing success & failures are recorded. Secondly, could you execute the following SQL code and post the output? use <your_database_name> go SELECT OBJECTPROPERTY (object_id('<table_name>'),'TableHasActiveFulltextIndex') SELECT OBJECTPROPERTY (object_id('<table_name>'),'TableFullTextBackgroundUpdateIndexOn') SELECT OBJECTPROPERTY (object_id('<table_name>'),'TableFullTextChangeTrackingOn') go select FulltextCatalogProperty('<FT_Catalog>', 'UniqueKeyCount') select FullTextCatalogProperty('<FT_Catalog>', 'itemcount') select FullTextCatalogProperty('<FT_Catalog>', 'indexsize') Have you or anyone else changed the MSSQLServer service startup account &/or password via Win2K's components service? Also, does the SQL Server login BUILTIN\Administrator exist on this server with all of it's normal defaults: master database, SysAdmin rights? Depending upon your answers, you should review the following KB articles: 317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate Catalogs http://support.microsoft.com/default.aspx?scid=kb;en-us;317746 277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component Services] http://support.microsoft.com/default.aspx?scid=KB;EN-US;277549 If the BUILTIN\Administrator account was removed for security reasons, you can use the following SQL code to get FTS to work without it: exec sp_grantlogin N'NT Authority\System' exec sp_defaultdb N'NT Authority\System', N'master' exec sp_defaultlanguage N'NT Authority\System','us_english' exec sp_addsrvrolemember N'NT Authority\System', sysadmin Regards, John -- SQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ [quoted text, click to view] "John Mott" <johnmott59@hotmail.com> wrote in message news:eHI51vy2FHA.1148@tk2msftngp13.phx.gbl... > this is a new windows 2003 server/sql server 2000 set up (2-3 days old). > full text is set up and working and definetely added data, andi have > update > index in background set. > > It seems to have worked for a while, then stopped. The application event > log > records events for Microsoft Search as 'Search service has started', > 'Search > service has loaded project ...' and 'indexer started successfully for > project..' from yesterday afternoon, about the time that it stopped > adding > articles. > > There is only one catalog and one table with two fields that are indexed. > > since there is data in there it seems to have stopped in the last day or > so. > the system is lightly loaded, nothign else going on, and i can see > mssearch.exe in the task list. > > where can i look to see why its not loading? > > thanks, > > john > > >
Thanks John,
the results of the queries were: 'TableHasActiveFulltextIndex' = 1 'TableFullTextBackgroundUpdateIndexOn = 1 'TableFullTextChangeTrackingOn' = 1 'UniqueKeyCount' = 109132 'itemcount' = 43844 'indexsize' = 60 I think I know what this might be related to, but i'm not sure how to change it. From an earlier version of this database i learned that in Windows 2003 in order to allow an .net web program to access the SQL server you have to add IIS_WPG account to the database, there apparantly isn't an account called 'system' anymore. I think the failure began when I added this account to the database; it had been running for a day or so just fine. So, there must be an interaction between IIS_WPG and this. I had tried to reproduce the setup for dbo for this account, but i'm very weak on SQL server administration so its quite likely that there is something missing. I went through and added privileges wherever i could find them. Yesterday I started an incremental build thats still running to try to catch up. will the update index work while a build is taking place? There are no error messages in the event logs or reports of an inability to access any resources. I wish the client could find an expert in this area; i'm very weak on SQL DBA and wish I could just focus on the software development. Everyone is either too busy or doesn't have the necessary depth.. john [quoted text, click to view] "John Kane" <jt-kane@comcast.net> wrote in message news:%237yZRV42FHA.320@TK2MSFTNGP10.phx.gbl... > John, > First you should review the server's "Microsoft Search" and MssCi source > events (informational, warnings & errors), as it is only in the Application > event log that SQL Server 2000 FT Indexing success & failures are recorded. > Secondly, could you execute the following SQL code and post the output? > > use <your_database_name> > go > SELECT OBJECTPROPERTY > (object_id('<table_name>'),'TableHasActiveFulltextIndex') > SELECT OBJECTPROPERTY > (object_id('<table_name>'),'TableFullTextBackgroundUpdateIndexOn') > SELECT OBJECTPROPERTY > (object_id('<table_name>'),'TableFullTextChangeTrackingOn') > go > select FulltextCatalogProperty('<FT_Catalog>', 'UniqueKeyCount') > select FullTextCatalogProperty('<FT_Catalog>', 'itemcount') > select FullTextCatalogProperty('<FT_Catalog>', 'indexsize') > > Have you or anyone else changed the MSSQLServer service startup account &/or > password via Win2K's components service? Also, does the SQL Server login > BUILTIN\Administrator exist on this server with all of it's normal defaults: > master database, SysAdmin rights? Depending upon your answers, you should > review the following KB articles: > > 317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate Catalogs > http://support.microsoft.com/default.aspx?scid=kb;en-us;317746 > > 277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify > MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K Component > Services] > http://support.microsoft.com/default.aspx?scid=KB;EN-US;277549 > > If the BUILTIN\Administrator account was removed for security reasons, you > can use the following SQL code to get FTS to work without it: > > exec sp_grantlogin N'NT Authority\System' > exec sp_defaultdb N'NT Authority\System', N'master' > exec sp_defaultlanguage N'NT Authority\System','us_english' > exec sp_addsrvrolemember N'NT Authority\System', sysadmin > > Regards, > John > -- > SQL Full Text Search Blog > http://spaces.msn.com/members/jtkane/ > > > "John Mott" <johnmott59@hotmail.com> wrote in message > news:eHI51vy2FHA.1148@tk2msftngp13.phx.gbl... > > this is a new windows 2003 server/sql server 2000 set up (2-3 days old). > > full text is set up and working and definetely added data, andi have > > update > > index in background set. > > > > It seems to have worked for a while, then stopped. The application event > > log > > records events for Microsoft Search as 'Search service has started', > > 'Search > > service has loaded project ...' and 'indexer started successfully for > > project..' from yesterday afternoon, about the time that it stopped > > adding > > articles. > > > > There is only one catalog and one table with two fields that are indexed. > > > > since there is data in there it seems to have stopped in the last day or > > so. > > the system is lightly loaded, nothign else going on, and i can see > > mssearch.exe in the task list. > > > > where can i look to see why its not loading? > > > > thanks, > > > > john > > > > > > > >
You're welcome, John,
When you say that you added IIS_WPG account to the database are you referring to adding this via the Enterprise Manager (EM) by clicking on the registered server, and expanding the database folder and adding a new "user" in the user folder? Or was this a change to the SQL Server (MSSQLServer)? If the former, then adding the IIS_WPG "user" account to the database should not make a difference. However, you mean the latter, i.e., change to the SQL Server (MSSQLServer), then this may be the problem, but it depends upon where it was done. Specifically, was it modified in the Enterprise Manager (EM) or in the Windows 2003's Administrative Tools -> Services? If the latter, then this change must be re-made, i.e., changed the Enterprise Manager's server security tab and then changed again back to the IIS_WPG account. When this change is made, you should be prompted to stop and re-start SQL Server. If the incremental build started and is running successfully, you will see "Microsoft Search" and MssCi source events in the server's application event log indication it progress (at 50K row increments) and success &/or failure. Depending if your table as a timestamp column and if the FT Catalog has been successfully populated before, then yes the Incremental Population may take some time to finish depending upon the number of rows in the table. This is normal and expected when Change Tracking and Updated Index in Background is first enabled. Yes, once this "re-sync" process is complete, then each time you update the FT-enabled column, then the FT Catalog will also be updated. FYI, there never was a 'system' account, but on the Services dialog and in the EM's security tab, it is referred to as "system account" and when you have enabled this account, you are in fact using the "LocalSystem" account and this account can not be removed or deleted. Hope that helps! John -- SQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ [quoted text, click to view] "John Mott" <johnmott59@hotmail.com> wrote in message
news:OjpNG$82FHA.1420@TK2MSFTNGP09.phx.gbl... > Thanks John, > > the results of the queries were: > > > 'TableHasActiveFulltextIndex' = 1 > 'TableFullTextBackgroundUpdateIndexOn = 1 > 'TableFullTextChangeTrackingOn' = 1 > > 'UniqueKeyCount' = 109132 > 'itemcount' = 43844 > 'indexsize' = 60 > > I think I know what this might be related to, but i'm not sure how to > change > it. From an earlier version of this database i learned that in Windows > 2003 > in order to allow an .net web program to access the SQL server you have to > add IIS_WPG account to the database, there apparantly isn't an account > called 'system' anymore. > > I think the failure began when I added this account to the database; it > had > been running for a day or so just fine. So, there must be an interaction > between IIS_WPG and this. I had tried to reproduce the setup for dbo for > this account, but i'm very weak on SQL server administration so its quite > likely that there is something missing. I went through and added > privileges > wherever i could find them. > > Yesterday I started an incremental build thats still running to try to > catch > up. will the update index work while a build is taking place? > > There are no error messages in the event logs or reports of an inability > to > access any resources. > > I wish the client could find an expert in this area; i'm very weak on SQL > DBA and wish I could just focus on the software development. Everyone is > either too busy or doesn't have the necessary depth.. > > john > > > > "John Kane" <jt-kane@comcast.net> wrote in message > news:%237yZRV42FHA.320@TK2MSFTNGP10.phx.gbl... >> John, >> First you should review the server's "Microsoft Search" and MssCi source >> events (informational, warnings & errors), as it is only in the > Application >> event log that SQL Server 2000 FT Indexing success & failures are > recorded. >> Secondly, could you execute the following SQL code and post the output? >> >> use <your_database_name> >> go >> SELECT OBJECTPROPERTY >> (object_id('<table_name>'),'TableHasActiveFulltextIndex') >> SELECT OBJECTPROPERTY >> (object_id('<table_name>'),'TableFullTextBackgroundUpdateIndexOn') >> SELECT OBJECTPROPERTY >> (object_id('<table_name>'),'TableFullTextChangeTrackingOn') >> go >> select FulltextCatalogProperty('<FT_Catalog>', 'UniqueKeyCount') >> select FullTextCatalogProperty('<FT_Catalog>', 'itemcount') >> select FullTextCatalogProperty('<FT_Catalog>', 'indexsize') >> >> Have you or anyone else changed the MSSQLServer service startup account > &/or >> password via Win2K's components service? Also, does the SQL Server login >> BUILTIN\Administrator exist on this server with all of it's normal > defaults: >> master database, SysAdmin rights? Depending upon your answers, you should >> review the following KB articles: >> >> 317746 (Q317746) PRB: SQL Server Full-Text Search Does Not Populate > Catalogs >> http://support.microsoft.com/default.aspx?scid=kb;en-us;317746 >> >> 277549 (Q277549) PRB: Unable to Build Full-Text Catalog After You Modify >> MSSQLServer Logon Account Through [NT4.0) Control Panel [or Win2K > Component >> Services] >> http://support.microsoft.com/default.aspx?scid=KB;EN-US;277549 >> >> If the BUILTIN\Administrator account was removed for security reasons, >> you >> can use the following SQL code to get FTS to work without it: >> >> exec sp_grantlogin N'NT Authority\System' >> exec sp_defaultdb N'NT Authority\System', N'master' >> exec sp_defaultlanguage N'NT Authority\System','us_english' >> exec sp_addsrvrolemember N'NT Authority\System', sysadmin >> >> Regards, >> John >> -- >> SQL Full Text Search Blog >> http://spaces.msn.com/members/jtkane/ >> >> >> "John Mott" <johnmott59@hotmail.com> wrote in message >> news:eHI51vy2FHA.1148@tk2msftngp13.phx.gbl... >> > this is a new windows 2003 server/sql server 2000 set up (2-3 days >> > old). >> > full text is set up and working and definetely added data, andi have >> > update >> > index in background set. >> > >> > It seems to have worked for a while, then stopped. The application >> > event >> > log >> > records events for Microsoft Search as 'Search service has started', >> > 'Search >> > service has loaded project ...' and 'indexer started successfully for >> > project..' from yesterday afternoon, about the time that it stopped >> > adding >> > articles. >> > >> > There is only one catalog and one table with two fields that are > indexed. >> > >> > since there is data in there it seems to have stopped in the last day >> > or >> > so. >> > the system is lightly loaded, nothign else going on, and i can see >> > mssearch.exe in the task list. >> > >> > where can i look to see why its not loading? >> > >> > thanks, >> > >> > john >> > >> > >> > >> >> >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |