There is some caution needed when working with FILESTREAM and clustered indexes. Specifically, if all of the following conditions are true, your FILESTREAM data ends up being moved to the default FILEGROUP even though this may not be your intent:
- Your database has more than one FILESTREAM filegroup.
- You have one or more tables that store FILESTREAM data on a non-default filegroup.
- You drop and re-create a clustered index on such a table.
When all these conditions are met, you will find that your FILESTREAM data has been moved (copied initially, until garbage collection takes place) to the default FILESTREAM filegroup.
Solution
In order to avoid this, when re-creating the clustered index, be sure to explicitly specify the FILESTREAM filegroup to use for that table (with the exception of partitioning rows, it’s not possible to have data in a single table spread across two FILESTREAM filegroups):
ALTER TABLE [dbo].[FILESTREAMtablename] ADD CONSTRAINT [PKEY] PRIMARY KEY CLUSTERED ([ID] ASC) -- Explicitly specifying the FILESTREAM filegroup causes no data to be moved (if the filegroup you specify is where the FILESTREAM data already is located FILESTREAM_ON [FILESTREAM_filegroup2]
Because SQL Server Management Studio does not support specifying the FILESTREAM filegroup to use when creating clustered indexes, you can only do this using T-SQL scripts.
If you have accidentally moved your FILESTREAM to an incorrect filegroup, you can recover by first making sure the garbage collector has removed all the files from the old location (this will normally involve backing up and truncating the transaction log, then executing a CHECKPOINT – more details about the garbage collector can be found in Paul Randal’s post). Then you can drop and re-create your clustered index again
Background and Discussion
The reason for this data move is directly related to the purpose of a clustered index. A clustered index determines the physical organization of the table data on disk, and if this physical organization changes, the entire table is re-written to disk.
Of course, the physical table data does not include FILESTREAM, so from that perspective, you may consider this behavior unexpected.
However, this behavior is by design, and the design is such that this is the way to move FILESTREAM data from one filegroup to another, which might be necessary if the volume on which the FILESTREAM filegroup is located is running out of space, or if you are upgrading hardware and need to migrate data to a new storage array.
Sven Aelterman 