SQL Server 2012, FILESTREAM and Full Index Catalogs
Posted on 2014-01-13
I've been trying to wrap my head around this newer FILESTREAM technology in SQL, and I followed a number of articles to the point I thought I had this up and running pretty well.
I'm trying to create a Resume Search system where we dump all of our Resume-related files (mostly Word, plus some TXT and searchable PDFs) and I can build applications involving somewhat complex file and content searches.
Initially, I manually copied all of our Resume files from our main file server into the FILESTREAM share on the SQL Server. The next day, I was able to run queries without a problem based on file information and file content keywords within a few seconds.
I wanted to set up a simple backup program (Karen's Replicator) to copy any new or modified Resume documents over from the file server to the FILESTREAM share. But because it has a feature to also Delete folders and files that aren't found, I didn't want to take the chance of it deleting folders or files on the file server so I deleted all of them on the FILESTREAM share, then ran the backup job which copied them all over again. That's when I started running into problems.
After the first backup job run, I tried running the same queries but it was taking forever. So I thought the reorganize job wasn't running, but according to the Job History, the following has been completing successfully each night and taking 18-28 or so seconds to complete:
ALTER FULLTEXT CATALOG [ResumeFullTextCatalog] REORGANIZE
I manually executed that last Friday and it was still running as of this afternoon (about 68 hours). It's only a total of 58,444 files totaling 4.68GB (located on an iSCSI Windows Share). I also tried the REBUILD option which only takes about 20 seconds. Should the REORGANIZE really take this long?