Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2012, FILESTREAM and Full Index Catalogs

Posted on 2014-01-13
4
Medium Priority
?
438 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

722 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