title: Delete Filegroup and File from database
body: <p>This is my database structure:</p>
  
  <ul>
  <li>4 filegroups: PRIMARY, FileGroup201708, FileGroup201709, FileGroup201710</li>
  <li>4 files: File1.mdf, File201708.ndf, File201709.ndf, File201710.ndf</li>
  </ul>
  
  <p>This is the partition scheme and Function:</p>
  
  <pre><code>CREATE PARTITION FUNCTION [MyFunction](datetime) 
  AS RANGE RIGHT FOR VALUES (N'2017-08-01T00:00:00.000', N'2017-09-01T00:00:00.000', N'2017-10-01T00:00:00.000')
  
  CREATE PARTITION SCHEME [MyScheme] 
  AS PARTITION [MyFunction] TO ([PRIMARY], [FileGroup201708], [FileGroup201709], [FileGroup201710])
  </code></pre>
  
  <p>All table in the database are partitioned.</p>
  
  <p>Well, I decided to delete all data for 201708 period, first I perform :</p>
  
  <pre><code>TRUNCATE TABLE MyTable WITH (PARTITIONS (partition_number)) 
  </code></pre>
  
  <p>for all table. After this operation there isn't any data for partition in table.</p>
  
  <pre><code>SELECT
      p.partition_number, fg.name, p.rows, OBJECT_NAME(p.object_id)
  FROM
      sys.partitions p
  INNER JOIN 
      sys.allocation_units au ON au.container_id = p.hobt_id
  INNER JOIN 
      sys.filegroups fg ON fg.data_space_id = au.data_space_id
  WHERE
      p.object_id = OBJECT_ID('MyTable')
  </code></pre>
  
  <p>The query above give me 0 rows for the specific filegroup.</p>
  
  <p>Now I want to delete the file and the filegroup, when I try to delete the file there is no problem, but when I try to delete a filegroup, I get an error from SQL Server: </p>
  
  <blockquote>
    <p>The filegroup 'FileGroup201708' cannot be removed because it is not empty</p>
  </blockquote>
  
  <p>I checked if there are some indexes in that filegroup using this query:</p>
  
  <pre><code>SELECT 
      OBJECT_NAME(i.[object_id]) AS [ObjectName],
      i.[index_id] AS [IndexID],
      i.[name] AS [IndexName],
      i.[type_desc] AS [IndexType],
      i.[data_space_id] AS [DatabaseSpaceID],
      f.[name] AS [FileGroup],
      d.[physical_name] AS [DatabaseFileName]
  FROM 
      [sys].[indexes] i
  INNER JOIN 
      [sys].[filegroups] f ON f.[data_space_id] = i.[data_space_id]
  INNER JOIN 
      [sys].[database_files] d ON f.[data_space_id] = d.[data_space_id]
  INNER JOIN 
      [sys].[data_spaces] s ON f.[data_space_id] = s.[data_space_id]
  WHERE 
      OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1
  ORDER BY 
      OBJECT_NAME(i.[object_id]), f.[name], i.[data_space_id]
  </code></pre>
  
  <p>But all rows have in the column "FileGroup", PRIMARY filegroup. Can anyone give some suggestion?</p>