none
Is it normal for the transaction log and full text catalog files to balloon in size when repopulating the full text catalog?

    Question

  • We are using SQL Server 2008 R2 SP1 x64

    We have a fairly large SQL-based system for archiving e-mails.  The primary data file is approximately 278GB in size, and we are running in "Full Recovery" mode with transaction log backups every 30 minutes.   There are four full-text catalogs that currently have the following sizes:

    • AttachmentText - 82GB
    • BodyText - 65GB
    • AddressDetails - 300MB
    • Word - 10MB

    The two at the top concern me because of their size.  Most of the time, the space within the files is less than 50% utilized (as presented by the "Disk Usage" report for the database).  The exception is during the weekly job that re-populates the full text catalogs.  During this time, the space within the full text catalog files becomes increasingly more utilized, occasionally triggering an auto-growth event.  Also affected is the transaction log, which has ballooned to 70GB+ in size.  After the catalog repopulation is complete, the transaction log has a ridiculous amount of free space within.  (Only a few hundred MB out of the 72GB reserved are being used).

    Is this normal behavior?  The vendor has shrugged their shoulders and indicated that we should ask Microsoft, even though we followed their recommendations is setting up the full text catalog rebuild process.  Is our only option to throw more storage at the problem, or is something causing this that shouldn't be happening?

    Monday, April 09, 2012 3:05 PM

Answers