Recently I began supporting a database with 16 filegroups, which in and of itself is not issue. However this particular database is only 7 GB in total used size and all of the 16 files are were located on a single EMC symmetrix volume. Because of the use of file groups the database had expanded to a total of 15 GB unnecessarily doubling its size. Although there are legitimate reasons to use filegroups; In this situation 16 file groups were clearly excessive and did not create substantial value since all of the files were located on a single volume. Although it could be argued that filegroups can aid in recovering certain tables without restoring the entire database, this type of recoverability was not needed. If you buy quality physical disks and back/recovery software you can avoid using filegroups entirely. So, I set out to remove 15 filegroups in favor of a single PRIMARY filegroup.

I wanted to remove files/filegroups by using scripts, so began by creating scripts to move all objects to the PRIMARY filegroup:

I began by backing up and restoring the existing production database to a QA environment, setting the recovery mode to simple, setting the default filegroup to primary and expanding the primary filegroup large enough to hold all database objects and accommodate index rebuilds/future growth:

ALTER DATABASE MyDB
SET RECOVERY SIMPLE
GO
ALTER DATABASE MyDB
MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
ALTER DATABASE MyDB
MODIFY FILE
   (NAME = MYDB_fg_primary,
   SIZE = 10000MB,
   MAXSIZE = 20000MB,
   FILEGROWTH=500MB)
GO

Once this had been accomplished I scripted out all clustered indexes and noclustered indexes for those table with clustered indexes and then rebuilt those indexes on the PRIMARY filegroup. Tables without a clustered index or without any indexes must be handled differently.

Drop the non-clustered indexes of tables with clustered indexes:

DROP INDEX TblWithClustIDX.IX_NonClustCol 

Drop the clustered index, which in this case is also the primary key constraint.

ALTER TABLE TblWithClustIDX DROP CONSTRAINT PK_TblWithClustIDX
GO

Once the non-clustered and clustered indexes have been dropped rebuild the clustered and non-clustered indexes on the PRIMARY filegroup:

ALTER TABLE TblWithClustIDX
    ADD     CONSTRAINT PK_TblWithClustIDX
    PRIMARY KEY CLUSTERED (ClustCol) 
ON [PRIMARY]
GO
 
CREATE NONCLUSTERED INDEX IX_ NonClustCol
    ON TblWithClustIDX(NonClustCol)
 
    ON [PRIMARY]
GO

For tables without a clustered index, but with a nonclustered index you can move the data pages to the primary filegroup by dropping the existing non-clustered index and recreating the index as clustered. Then you can return the index to it's original, nonclustered state by dropping and recreating again on the PRIMARY filegroup:

For example, TblWithClustIDXOnly does not have a clustered index, only nonclustered indexes.

Drop existing nonclustered index, which in this case is a primary key.

CREATE UNIQUE CLUSTERED
  INDEX PK_TblWithClustIDXOnly ON dbo.TblWithClustIDXOnly (NonClustCol)
WITH
    DROP_EXISTING
ON PRIMARY
GO

Drop the clustered index you've just created.

ALTER TABLE TblWithClustIDXOnly DROP CONSTRAINT PK_TblWithClustIDXOnly
GO

Recreate the nonclustered index on the primary filegroup.

ALTER TABLE TblWithClustIDXOnly
    ADD     CONSTRAINT PK_TblWithClustIDXOnly
    PRIMARY KEY NONCLUSTERED (NonClustCol)
ON PRIMARY
GO

For tables without indexes you can simply move their data pages to the primary filegroup by selecting them into another database, dropping the existing table from the original and then selecting the table back into the original database.

SELECT * INTO DBObjCopy.dbo.NoIndexesTable FROM NoIndexesTable
GO
DROP TABLE NoIndexesTable
GO
SELECT * INTO dbo.NoIndexesTable FROM DBObjCopy.dbo.NoIndexesTable
GO

At this point I thought I was done and could safely drop the files. However when I attempted to drop several of the filegroups SQL Server returned an error message indicating the file could not be dropped because it was not empty (SQL Server will not let you drop a file or filegroup if it is not empty). So I set out to determine which objects were still located on a filegroup other than the primary group. The undocumented stored procedure sp_objectfilegroup will list the filegroup for an object provided you pass it the object_id, but I did not know the object_id plus I wanted to run an object to filegroup query for all objects. Using sp_objectfilegroup as a starting point and building on the query used by sp_objectfilegroup; I came up with a query to show all of the table/object names that are located on a filegroup other than primary:

--Listing 1:  T-SQL to Display objects and filegroups not on Primary Filegroup
select TableNAME = o.name, ObjectName = i.name, i.indid, s.groupname
                  from sysfilegroups s, sysindexes i, sysobjects o
                  where i.id = o.id
                        and o.type in ('S ','U ') --system or user table
                        and i.groupid = s.groupid
AND s.groupname <> 'PRIMARY'
 
--indid values
-- 1 clustered index
-- 0 heap
-- 255 text/image
-- > 1 nonclustered index or statistic

After I running the query I could see I had several hundred objects still located on the various filegroups. Since I'd taken care of the clustered indexes and the heaps (both with and without nonclustered indexes), the indid and name indicated the remaining objects were of two types: text/image columns and statistics. All but a few of them were system-generated statistics and the rest were text/image columns. SQL Server had created hundreds of system-generated statistics (all are named an _WA%), which appeared to be located on the same filegroup as the table's original filegroup.

I could simply drop all the system-generated statistics, however I didn't want to take the performance hit of re-generating these statistics during production hours. So I created another script to drop all of the statistics and re-create them. When they were recreated they were then located on the primary filegroup.

Listing 2: T-SQL to Drop and recreate all statisitics
SET NOCOUNT ON
GO
 
create table #stat
(stat_name sysname,
stat_keys varchar(1000),
table_name varchar(100))
Go
 
DECLARE tab CURSOR
READ_ONLY
FOR SELECT table_name FROM information_schema.tables
 
DECLARE @name varchar(40)
OPEN tab
 
FETCH NEXT FROM tab INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
            insert into #stat(stat_name,stat_keys)
            EXEC sp_helpstats @name
            update #stat
            set table_name = @name
            where table_name is null
      END
      FETCH NEXT FROM tab INTO @name
END
 
CLOSE tab
DEALLOCATE tab
GO
 
PRINT 'PRINT ''<<< DROPPING STATISTICS >>>'''
GO
 select 'DROP STATISTICS ' + TABLE_NAME + '.' + STAT_NAME + '
 ' + 'GO' from #stat
GO
PRINT 'PRINT ''<<< DROP STATISTICS COMPLETE >>>'''
GO
PRINT 'PRINT ''<<< CREATING STATISTICS >>>'''
GO
 select 'CREATE STATISTICS ' + STAT_NAME + '
 ' + 'ON MyDB..' + TABLE_NAME + '(' + STAT_KEYS + ')' + '
 ' + 'GO' from #stat
GO
PRINT 'PRINT ''<<< CREATE STATISTICS COMPLETE >>>'''
GO

Once statistics were moved to the primary filegroup, all I had left to do was move the text/image columns. For this and only this portion I chose to use Enterprise Manager. Using the filegroup query in Listing 1, I identified 8 tables with text/image columns not located on the primary filegroup. To use EM to move text/image columns to a new filegroup go to Design Table >> Properties and select the new filegroup from the Text Filegroup drop down list.

Since I had moved all tables, indexes and statistics to the primary filegroup by using scripts—I thought it would be nice to do the same with the text/image columns. Using profiler I traced the process of using Enterprise Manager to change text/image column filegroup. Unfortunately I discovered that SQL Server would drop and recreate the table in order to move the text/image column to primary filegroup. Although I could have accomplished moving to a new filegroup by backing up the table via “select into…” and dropping and recreating I felt it would be easier to just let Enterprise Manager do this and since it was only 8 tables I didn't mind manually changing these columns via Enterprise Manager.

Once the text/image columns had been moved I ran the query in Listing 1 and finally, there were no objects located on any filegroup other than the primary filegroup. Once again I attempted to drop the files and SQL Server again returned an error message indicating the files were not empty. I then shrank the files using DBCC SHRINKFILE and was finally able to the drop the files and filegroups.

Mission accomplished! You're probably wondering if this all was worth it? Yes, I could have used Enterprise Manager entirely instead of scripts, however going through 170 tables manually changing filegroups did not appeal to me. Also because of production refreshes of QA, ongoing testing of filegroup moves and production implementation I would have to go through this whole process at least a half dozen more times. So, it did save me time. I would rather use testable, repeatable scripts to move changes into production instead of error prone labor-intensive processes utilizing Enterprise Manager.