Solved

SQL Server 2012, FILESTREAM and Full Index Catalogs

Posted on 2014-01-13
4
421 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

943 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

15 Experts available now in Live!

Get 1:1 Help Now