Solved

Filestream index randomly changing filegroup

Posted on 2014-01-23
3
369 Views
Last Modified: 2014-01-24
I have a table with a filestream column holding about 2000 photographs. When I created it I set the primary key to the unique identifier column (probably an unnecessary step). I checked table properties and it was assigned to the filestream filegroup. Everything OK for a couple of weeks, repopulating the table many times without incident. Just now I have discovered that the index has moved itself from the filestream filegroup to the default secondary (user data) filegroup. After repopulating the table I got the message:

Could not allocate space for object 'dbo.T_AllPhotos' in database 'myDB' because the 'FG_UserData' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

By way of confirmation I checked the table's filegroup in table properties and queried sys.filegroups/sys.indexes, and it has indeed moved. I've now dropped the primary key on this table (there are no other indexes), but am still stuck with this table being in the wrong filegroup. The only thing that changed recently was that I added a new varchar column to the table, not indexed. Can this really be responsible for filegroup changing?
0
Comment
Question by:TimHudspith
  • 2
3 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 39803141
create a clustered index on the filestream filegroup.
0
 

Author Comment

by:TimHudspith
ID: 39805601
Can you guide me on the synatax? The following gives me the error 'invalid filegroup specified'.

CREATE UNIQUE CLUSTERED INDEX ux_AllPhotos_Filename
ON dbo.T_AllPhotos(Filename)
ON [FG_Filestream]

Open in new window

0
 
LVL 18

Accepted Solution

by:
x-men earned 500 total points
ID: 39805985
for table :
                                           
CREATE TABLE dbo.T_AllPhotos
      (
      EmployeeId int
      ,[filename] varbinary(max) FILESTREAM NULL
      ,MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL
          UNIQUE DEFAULT NEWID()
      );



CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20140124-094404] ON [dbo].[T_AllPhotos]
(
      [EmployeeId] ASC
) FILESTREAM_ON [FG_Filestream]
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 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