September 23, 2017 at 2:50 pm
large database of about 2TB... Full text indexing on various tables with the majority of data being from one large table with change tracking turned on. Had some issues with disk space previously and dropped the whole indexes and catalog and was going to recreate. Created catalog on file group (ft file) and when it populates....the FT catalog file does not grow...but the database is growing!!! What gives? The catalog file should reach about 600GB....but instead the process is about 80 percent done and the database itself has grown like 400GB... What gives? Is this normal? What's going on? I'm not at home so I can't test right at the moment....but something seems wrong.
September 23, 2017 at 4:50 pm
taseedorf - Saturday, September 23, 2017 2:50 PMlarge database of about 2TB... Full text indexing on various tables with the majority of data being from one large table with change tracking turned on. Had some issues with disk space previously and dropped the whole indexes and catalog and was going to recreate. Created catalog on file group (ft file) and when it populates....the FT catalog file does not grow...but the database is growing!!! What gives? The catalog file should reach about 600GB....but instead the process is about 80 percent done and the database itself has grown like 400GB... What gives? Is this normal? What's going on? I'm not at home so I can't test right at the moment....but something seems wrong.
I found this...is it possible this table fills when you have change tracking on, then is removed?
That's why I recommend you enable change tracking with background or scheduled updating. If you do, you'll see that MSSearch will first issue another:
sp_fulltext_getdata 10, 517576882, 0x3131312D31312D31313131,1
for every row in the table with change tracking enabled. Again, 0x3131312D31312D31313131 is the primary key value for the first row. Then, for every row that has a column that you're full-text indexing and that's modified after your initial full population, the row information will be written (in the database you're indexing) to the sysfulltextnotify table. MSSearch will then issue the following only for the rows that apear in this table–and will then remove them from the sysfulltextnotify table.
September 24, 2017 at 11:15 pm
Greetings,
You could partition the huge SQL server table to partitions to alleviate the heavy burden. Then you can apply indexes much easier to different file groups. Please see additional comments on the Web site below: (Sometimes, these Web sites may not provide good answers)
https://dba.stackexchange.com/questions/78554/sql-server-2012-full-text-catalog-taking-up-all-the-disk-space
Perhaps, you can detach, and re-attach the database in question. I am not sure if this would help.
As always, test the options in a test platform.
Regards,
DBASupport
September 25, 2017 at 10:56 am
Figured out the issue!!
When I was creating the index, I was adding it to the "default file group"...instead of specifying the one I created for full text. When you add it via script and omit that, it appears to automatically use the default file group versus the one you created for FTC....grrrrrrrr
SOLVED!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply