.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Maintenance plan to rebuild Full-text index ? (SQL 2005)

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
Is it possible, through the GUI Wizard, to create a Maintenance plan that rebuilds (rather than reorganise/optimise) a Full text index in 2005 ? The full text index is based on one table only - so would using the "Rebuild index task" in the Wizard do the trick ? I wouldn't mind if the other indexes on the table were rebuilt too, but main concern in the Full text Index. If not - then I guess it would be a scheduled job which does a ALTER FULLTEXT CATALOG catalog_name REBUILD, but I thought it would be nice to keep it within the - Management - Maintenance plans - section of SQL Management Studio. Also - should this be preceeded with a transaction log backup ? Had some issues rebuilding them last night - seemed a Log backup was needed.

View Complete Post

More Related Resource Links

Does a Maintenance Plan "Rebuild Index" task on Tables + Views rebuild the full text indexes ?


As subject really.

I'm talking about a complete rebuild - not an incremental, or a re-organise.

SQL 2005

The full text indexes are only on individual tables, not Views.

I've asked a similar question before, but sufficiently different, and other is marked as Answered so thought I'd open a new Q : http://social.msdn.microsoft.com/Forums/en/sqltools/thread/5f17f4f1-fba9-436f-aedf-cfbfd89c8db2)


SQL 2005 Full Text Indexing


I am trying to create a full-text index on a table in my SQL 2005 Standard database. I ran into 2 issues:

  1. I had to execute the sp_fulltext_database procedure to enable Full-Text Indexing. According to the docs I should not have had to do this as "all SQL 2005 databases are enabled for full-text indexing". No biggie (one would think)...
  2. After I create the table, a PK index and a unique index over the INTEGER ID column, I create the Full-Text catalog. So far so good. However, when I try to create the Full-Text index, I get the following error message: "Msg 7653, Level 16, State 1, Line 1 'IAK1_TR_TextFullText_1' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, and has maximum size of 900 bytes. Choose another index for the full-text key."

I have tried both the PK index and the Unique index. Both indexes are created over a single column (ID) which is an INTEGER, NOT NULL. Both indexes appear to conform to all of the requirements for the KEY INDE but I canot successfully create the Full Text Index.

Select @@v

MSFTE Errors during initial full-text index population


Error log:

full-text crawl logs for details.

2007-06-01 07:33:55.63 spid25s     Error: 7683, Severity: 16, State: 1.

2007-06-01 07:33:55.63 spid25s     Errors were encountered during full-text index population for table or indexed view '[XXXX].[dbo].[RECORDS]', database 'XXXX' (table or indexed view ID '738101670', database ID '17'). Please see full-text crawl logs for details.

2007-06-01 07:33:55.63 spid25s     Changing the status to MERGE for full-text catalog "XXXX" (21) in database "XXX" (17). This is an informational message only. No user action is required.


Maintenance Plan - Full Backup


Hey all

I am running a maintenance plan (Sql Server 2008 Enterprise) every night at mid-night. All the plan entails is doing a full backup of one database (about a 3GB database). We are getting timeouts on our site at exactly that point when the database is being backed up and they last up to 20min. I was under the impression that doing backups this way does not take the db down? Or am I wrong about this?

Has anyone else experienced this and if so what steps did you take to rectify it? Sorry if I have not provided other information, just let me know and I will give it to you.


Enable Full Text Index in VWD 2008 / SQL Server Express 2008


 Hi, I am using VWD 2008 Express and SQL Server 2008 Express. I want to enable full text index in some of my table. How do I do that? I only have  SQL Server Management Studio Express 2008. As far as I know, I cannot use SQL Server Management Studio Express 2008 to update the full text catalog according to : http://msdn.microsoft.com/en-us/library/ms365247.aspx

My tables are also not enable Full Text Search by default. 


 Any help would be very much appreciated!

 Edit 1: Someone had a similar problem to mine on this link: http://forums.asp.net/t/1169961.aspx
I installed the Sql Server 2008 Express with Advance Services and made sure that Full Text functionality is installed. However I don't get a full text menu in SQL Server Management Studio. 

 Edit 2: I uninstall and reinstall SQL Server 2008 Express with Advance Services but still couldn't be able to enable full text index.

 Edit 3: After see this, I guess I have to do it manually then:  http://forums.microsoft.com/MSDN/ShowPost.aspx?siteid=1&PostID=3912985

index rebuild (offline) on sql 2005 sp3 enterprise causes drastics growth of the database size(mdf)


index rebuild (offline) on sql 2005 sp3 enterprise causes drastics growth of the database size(mdf)

database grew from 40 GB to 500 GB. space is reserved no free space to reclaim after reindex completes.

pls guide

SQL 2005 "Class Not Registered" error when saving a Maintenance Plan


We are running SQL 2005 Standard Edition SP3 on a single server. Anytime I attempt to save a maintenance plan, I get a "Class Not Registered" error. I also got this when attempting to edit or delete Maintenance Plans created previously, when this functionality was working. On this server there is a SQL Server 2000 as the default instance, and the SQL Server 2005 is a named instance.

Looking at other posts everywhere, installing SP2 or SP3 can break the ability to create or edit Maintenance Plans. I can confirm that SSIS is running and that I can now connect to Integration Services in Mainagement Studio (and I was able to delete my old plans in the Integration Services). There was a problem with this which was cured by manually registering dts.dll and msDtsSrvrUtil.dll in the DTS/Binn folder. I wonder if I need to manually register any more DLLs.

For a full text index, what is the difference in these types of population schedules?


I am using SQL2008 and created a full-text index on 1 of my tables. Going through the wizard, I was offered (2) types of population schedules as follows:

New Table Schedule: Define a population schedule for a table.

New Catalog Schedule: Define a population schedule for a full-text catalog.

I am familiar with a 'Catalog' schedule for population which I do nightly and was similar to configuration on SQL2000 for a full text index.  However, what is the difference for a 'Table' schedule vs the 'Catalog' population schedule? I have the definitions above, but if someone with knowledge could offer a better in depth explanation in comparison, and if I should use both or not (I have just a Catalog population scheduled currently), this would be helpful.


full-text search in SQl 2005 and 2008


Considering that full text search engine is now integrated into SQL in 2008 version comparing to 2005, has anybody notice any difference in full text search speed after migrating from 2005 to 2008 using the same type of hardware?



Index Rebuild in SQL 2005


I am doing a single pk index rebuild on a 100 gig table.  After running sp_who2, I see that the rebuild in suspended state.  Over a period of time, I have noticed that neither disk io nor cpu io is changing.   Is there a way for me to cancel it?  Is it frozen forever?

Any help is appreciated 

Backup Maintenance Plan Failure SQL Server 2005

Hi All,
I have three maintenance Plans Full, Differential and Transaction Log backup. Full and Transaction Log backup plans are working fine. In the all the maintenance plans I am taking backup of almost 60 databases.

The problem is with the differential backup plan. The backup plan is keep failing with the following errors:

FailedSad-1073548784) Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'MY_Database' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'My_Database' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Giants_Security_4'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'E:\\MSSQL.1\\MSSQL\\Backup\\MY_Database\\MY_database_backup_200808240500.DIFF.BAK' WITH  FILE =

Many Missing Column Statistics errors on full-text index fragments, even after reorganizing catalog


I seem to have a large number of full-text index fragments, even after reorganizing both full-text catalogs. (SQL 2008)

While reviewing the results of the default trace, I noticed many “Missing Column Statistics” errors.  Seeing as how there are over a thousand of these errors within a 12 hour period, I figure this is something I should take care of. 

From trace, text data column:

  NO STATS:([MyDBName].[sys].[ifts_comp_fragment_2014778570_51269].[colid], [MyDBName].[sys].[ifts_comp_fragment_2014778570_51269].[pid], [MyDBName].[sys].[ifts_comp_fragment_2014778570_51269].[docidmax], [MyDBName].[sys].[ifts_comp_fragment_2014778570_51269].[dupseq])

When I query sys . fulltext_index_fragments , I see about 15 different fragments for each catalog, all with a status of 4.  I expected to see many more fragments listed, with a status of 0 maybe?  I guess I’m misunderstanding something.

After reorganizing the catalogs, I’m still seeing these

A strange question about maintenance plan in sqlserver 2005


This is SQLServer 2005EE.

I created one maintenance plan some time ago.

But today i found i can't execute it and drop it.

the error message is:"exception has been thorwn by the target of an invocation.(mscorlib)"

The strange thing is,now i can create a new MP and drop it.

I have not found any valuable clues on the web sites.

could you please help me?


If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Maintenace Plan - Index online rebuild failed



on my sql 2008, using Wizard, I created a Maintenance Plan with one of the tasks - to rebuild indexes online. It is important for us that indexes are rebuilt online.

I've got the following error:

Executing the query "ALTER INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )
" failed with the following error: "Online index operation cannot be performed for index 'aspnet_Membership_index' because the index contains column 'Comment' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What are my options?

thank you!

Point In Time Recovery of Database with Full-Text Search enabled SQL 2005


Is this possible? 



Backup made at 10:30

changes made in system

Tail of the log backup taken at 10:35

Restore to point in tim 10:33 

At this point I get the error System.data.sqlclient.sqlerror: The File "sysft_yadayada_1" was not fully restored by a database or file restore.  The entire file must be successfully restored before applying this backup set.

BUT when I restore the database to most recent possible I have no issues..

when I do a select * from sys.master_files    sysft_yadayada_1 does not exist in that list, but  sysft_yadayada_5 does... When we were initially configuring fulltext search for this application we had a couple hiccups first till we got it working and thats the filename that ended up sticking.


Controls: Plan Your Migration to the Visual Studio 2005 Navigation Controls


Navigation is central to a well functioning Web application and it can be implemented numerous ways using today's technologies. This article highlights some of the key details of the new navigation controls that are part of the upcoming ASP.NET 2.0, and talks about how to design and implement one of these navigation controls today. This article contains a simple Web site that demonstrates the use of these navigation controls, pointing out some of the key items such as a breadcrumb control and things to watch for along the way. The article then describes how to implement a breadcrumb control today.

Dave Donaldson and Steven DeWalt

MSDN Magazine June 2004

Full text search in a Word document



  I´m currently programming a web search application in ASP.Net and having problem with full text searching. My requirements are:

1. Save a byte array from SQL server to word document as a temp file in a server side?

2. How to highlight the string I am currently searching in the Word document and return to the user the sentence with the highlighted string (like on Bing or any other search engines)? Is it possible to fullfill this requirement with full-text search integrated in SQL Server 2008?

Thanks in advance,




ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend