Solved

SQL Server 2012, FILESTREAM and Full Index Catalogs

Posted on 2014-01-13
4
434 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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 143

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

623 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