Solved

SQL Server 2012, FILESTREAM and Full Index Catalogs

Posted on 2014-01-13
4
419 Views
Last Modified: 2014-01-15
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:

USE [RESUMES]
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?
0
Comment
Question by:wchestnut
  • 2
  • 2
4 Comments
 

Accepted Solution

by:
wchestnut earned 0 total points
ID: 39783765
Well, even though it was disappointing not to get a response here, the issue has been resolved.  Apparently something was tying up access to the Catalog's Index.  I rebooted the server and the REBUILD finished quickly and my queries are running fast.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39783833
sad to hear that you seem disappointed.
however, your case was surely not the "easy-to-track-down" part, you could not tell yourself what exactly was going on.
glad you closed the question so it goes to PAQ
a3, zone advisor
0
 

Author Comment

by:wchestnut
ID: 39783853
Not receiving any kind of reply within 2 days should be disappointing to anyone here.  I usually get  a reply the same day if not hour -- at least with a suggestion or two.  And if I could tell what was going on myself, I wouldn't have had to ask the question.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39783860
Well, I can understand your frustration.
while I had seen (and subscribed to) your question, I couldn't check this kind of issue out, being "out of office" since monday, so not able to connect to my test sql servers.
I wish you the best for your next questions
a3
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now