Getting Rid of Excessive Files and Filegroups in SQL Server 7.0/2000

 

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.