Spilt SQL Server Datafile to multiple files

Spilt SQL Server Datafile to multiple files

Check the Datafile and space used

Use AdventureWorks;
SELECT df.name ,df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB,
df.size/128.0 as CurrenSizeinMB,mf.size*8192/1024 AS InitialSizeinKB
FROM sys.database_files df join
sys.master_files mf on df.name = mf.name and database_id = DB_ID()
go


DBCC SHOWFILESTATS

Show the space information in the extents context.

Use AdventureWorks;
DBCC SHOWFILESTATS;


Check tables/index belong to which file group


select  t.name as TableName,
        i.name as IndexName,
        fg.name as FielGroup,
        i.type,
        i.type_desc,
        t.type,
        p.rows as Rows
    from sys.filegroups fg join sys.database_files df
        on fg.data_space_id = df.data_space_id join sys.indexes i
        on df.data_space_id = i.data_space_id join sys.tables t
        on i.object_id = t.object_id join sys.partitions p
    on t.object_id = p.object_id and i.index_id = p.index_id
    --where fg.name = 'primary' and t.type = 'U'
    order by rows desc

ADD new file group and new datafiles



USE [master]
GO
ALTER DATABASE [AdventureWorks] ADD FILEGROUP [SALES]
GO
ALTER DATABASE [AdventureWorks] ADD FILE
( NAME = N'Sales_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AdventureWorks_SalesData.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [SALES]
GO



Create/recreate the cluster index and place it into file group.


create the test table

SELECT *
INTO newsales
FROM sales.Individual;

CREATE CLUSTERED INDEX IX_CustomerID
    ON newsales (customerID);
GO
Check the filegroup size and the location of the filegroup

Use AdventureWorks;
SELECT df.name ,df.size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB,
df.size/128.0 as CurrenSizeinMB,mf.size*8192/1024 AS InitialSizeinKB
FROM sys.database_files df join
sys.master_files mf on df.name = mf.name and database_id = DB_ID()
go

select  t.name as TableName,
        i.name as IndexName,
        fg.name as FielGroup,
        i.type,
        i.type_desc,    
        t.type,
        p.rows as Rows
    from sys.filegroups fg join sys.database_files df
        on fg.data_space_id = df.data_space_id join sys.indexes i
        on df.data_space_id = i.data_space_id join sys.tables t
        on i.object_id = t.object_id join sys.partitions p
    on t.object_id = p.object_id and i.index_id = p.index_id
    where t.name='newsales'


Add new filegroup

USE [master]
GO
ALTER DATABASE [AdventureWorks] ADD FILEGROUP [SALES]
GO
ALTER DATABASE [AdventureWorks] ADD FILE
( NAME = N'Sales_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AdventureWorks_SalesData.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [SALES]
GO


Recreate the cluster on the different filegroup.

USE [AdventureWorks];
CREATE CLUSTERED INDEX IX_CustomerID
    ON newsales (customerID)
    WITH(
        DROP_EXISTING = ON
        -- ONLINE = ON
        )
    ON SALES
GO


DBCC Shrink file emptyfile

Another way to move the data to other files is via DBCC SHRINKFILE emptyfiles. This would only move the data between data files in the same file group.

ALTER DATABASE [AdventureWorks] ADD FILE
( NAME = N'AdventureWorks_Data_new1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AdventureWorks_Data1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO
ALTER DATABASE [AdventureWorks] ADD FILE
( NAME = N'AdventureWorks_Data_new2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\AdventureWorks_Data2.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]
GO


Lets use DBCC SHRINKFILE with emptyfile option to move the data.

USE [AdventureWorks];
DBCC SHRINKFILE ( AdventureWorks_Data,emptyfile);


We can use DBCC SHOWFILESTATS to monitor the extend move status