December 14, 2017 at 3:07 am
Hello
I have never really done anything with full text catalogs apart from install the service, but as a DBA, should I be doing regular rebuilds in the normal index maintenance window?
We recently had a catalog which stopped updating and required a rebuild so I was thinking of automating this.
December 14, 2017 at 8:10 am
SQLAssAS - Thursday, December 14, 2017 3:07 AMHelloI have never really done anything with full text catalogs apart from install the service, but as a DBA, should I be doing regular rebuilds in the normal index maintenance window?
We recently had a catalog which stopped updating and required a rebuild so I was thinking of automating this.
Monitoring the fragmentation level with sys.fulltext_index_fragments is a good practice. Microsoft recommends alter fulltext index...reorganize as a part of regular maintenance. At what point to reorg or rebuild isn't really documented Microsoft just says to do this "periodically".
There are posts on other sites with some general recommendation regarding when to reorg/rebuild based on fragmentation - this is a decent reference:
How to automatically maintain Full-Text indexes and catalogs
The article has some other links as well that are worth reading. There are some sample scripts of what others are using to monitor and automate things.
Sue
December 14, 2017 at 8:20 am
Sue_H - Thursday, December 14, 2017 8:10 AMSQLAssAS - Thursday, December 14, 2017 3:07 AMHelloI have never really done anything with full text catalogs apart from install the service, but as a DBA, should I be doing regular rebuilds in the normal index maintenance window?
We recently had a catalog which stopped updating and required a rebuild so I was thinking of automating this.
Monitoring the fragmentation level with sys.fulltext_index_fragments is a good practice. Microsoft recommends alter fulltext index...reorganize as a part of regular maintenance. At what point to reorg or rebuild isn't really documented Microsoft just says to do this "periodically".
There are posts on other sites with some general recommendation regarding when to reorg/rebuild based on fragmentation - this is a decent reference:
How to automatically maintain Full-Text indexes and catalogsThe article has some other links as well that are worth reading. There are some sample scripts of what others are using to monitor and automate things.
Sue
Sue_H - Thursday, December 14, 2017 8:10 AMSQLAssAS - Thursday, December 14, 2017 3:07 AMHelloI have never really done anything with full text catalogs apart from install the service, but as a DBA, should I be doing regular rebuilds in the normal index maintenance window?
We recently had a catalog which stopped updating and required a rebuild so I was thinking of automating this.
Monitoring the fragmentation level with sys.fulltext_index_fragments is a good practice. Microsoft recommends alter fulltext index...reorganize as a part of regular maintenance. At what point to reorg or rebuild isn't really documented Microsoft just says to do this "periodically".
There are posts on other sites with some general recommendation regarding when to reorg/rebuild based on fragmentation - this is a decent reference:
How to automatically maintain Full-Text indexes and catalogsThe article has some other links as well that are worth reading. There are some sample scripts of what others are using to monitor and automate things.
Sue
Sue, I like you!! Bright as they come and your answers are always spot on. I have built a Maint system complete with it's own DB and tables for logging and such. It includes capability to automatically include new databases as added with default settings. One setting is a defrag frequency by database, default 30 days. It also does a daily eval of the fragmentation levels and records actual readings a trend from historical so that I can tweak the frequency. I am a sql developer and do not practice in the DBA space much but of course I touch on it.
Long story short yes make sure you keep your eye on the ball with regards to fragmentation.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 14, 2017 at 11:35 am
Jeffery Williams - Thursday, December 14, 2017 8:20 AMSue, I like you!! Bright as they come and your answers are always spot on. I have built a Maint system complete with it's own DB and tables for logging and such. It includes capability to automatically include new databases as added with default settings. One setting is a defrag frequency by database, default 30 days. It also does a daily eval of the fragmentation levels and records actual readings a trend from historical so that I can tweak the frequency. I am a sql developer and do not practice in the DBA space much but of course I touch on it.Long story short yes make sure you keep your eye on the ball with regards to fragmentation.
Thank you for the kind words - my mother will be happy to finally have some company 🙂
Sue
December 14, 2017 at 11:41 am
Sue_H - Thursday, December 14, 2017 11:35 AMJeffery Williams - Thursday, December 14, 2017 8:20 AMSue, I like you!! Bright as they come and your answers are always spot on. I have built a Maint system complete with it's own DB and tables for logging and such. It includes capability to automatically include new databases as added with default settings. One setting is a defrag frequency by database, default 30 days. It also does a daily eval of the fragmentation levels and records actual readings a trend from historical so that I can tweak the frequency. I am a sql developer and do not practice in the DBA space much but of course I touch on it.Long story short yes make sure you keep your eye on the ball with regards to fragmentation.
Thank you for the kind words - my mother will be happy to finally have some company 🙂
Sue
HUH? I am keeping your mom company? How did that happen lol.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 14, 2017 at 11:59 am
Jeffery Williams - Thursday, December 14, 2017 11:41 AMHUH? I am keeping your mom company? How did that happen lol.
The two people who like me....
Sue
December 14, 2017 at 12:02 pm
Sue_H - Thursday, December 14, 2017 11:59 AMJeffery Williams - Thursday, December 14, 2017 11:41 AMHUH? I am keeping your mom company? How did that happen lol.The two people who like me....
Sue
Awee.. I am sure that is not true. I mean I like your work. I don't actually know you, clearly.. But I am sure you are great.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
December 15, 2017 at 2:29 am
I feel a little bit like cupid on this thread but thanks for your responses 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply