August 7, 2009 at 8:15 am
What is difference b/w fulltext index and table indexs?
I have a maintenance plan in which i want to use Alter index only when, where there is need otherwise skip for next. how to i know this? and same as when to execute rebuild or reorganize? based on stats. how to i know this situation?
Need some sampling code if possible.
Shamshad Ali.
August 7, 2009 at 9:47 am
This is an issue on you knowing the DB you maintain. It is sugested that you check the fragmentation of an index at the end of the day and if it is above 35% (my rule of thumb) then you should reindex. If it doesn'e get fragmented to that point till a whole week then a weekly rendix will suffice. I reindex every morning on our DB's with high transactions in the umpteen GB transacation per day. These DB's range from the 80-120GB in size.
August 7, 2009 at 1:09 pm
See this article for information about full-text indexing.
Michelle Ufford aka sqlfool has an excellent script[/url] on her blog that handles index maintenance.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 8, 2009 at 5:02 am
Is the "excellent script" from blog related to FullText Index? Please help. Actually, we have just started working on FullText searching enabled application and have no idea how to managed it in SQL Server 2005, how frequently Catalog is required to reorganize or rebuild it or when it is required.
On development server we are facing Full Text search service hanging issue on daily bases, when we come in morning the search won't work, and give timeout errors - it won't work until we re-start Server. one of developer scheduled it to run every hr. ALTER FULLTEXT CATALOG catalog_name REORGANIZE the job run every hr untill backup starts then search hangs.
Error: 3023, Severity: 16, State: 2.
Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
I suspect this is becase of the autoshrink property was ON on this server and it might be conflicting with the REORGANIZE work.
we are trying to fix this problem in such a way that maintenance plan won't conflict during back up with Catalog REORGANIZE activity. or atleast we have idea how frequently we may use ALTER FULLTEXT CATALOG catalog_name REORGANIZE -
One more question, during maintenance i want one job completes then run another job. How do i do this - suppose first i have to run shrink DB then reindex then back db - so if shrink takes time, reindex won't start until shrink db completes similarly back up db won't start until reindex finishes first. Please help. ......
Shamshad Ali.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply