Split .mdf datafile methods

Question

We have a database with 1,1TB .mdf file in SQL Server 2014. I want to split it into two datafiles (add new .ndf file). As I know, there are two methods:

1. To rebuild big clustered indexes and then shrink the .mdf file.
2. Add new .ndf file and run a DBCC SHRINKFILE ('filename', EMPTYFILE). After this, new values will be replaced in both datafiles.

Which method is safer, faster, better? We will do first on TEST and then in PRODUCTION environment.

And maybe there is another way to split datafile?

Answer 1

I did this on an instance that had 20+ databases, some went fairly quick (minutes/hours). One was heavy OLPT and I ended up using a job that ran off hours to move, it took about 6 months.

I have tried, file balancing with index rebuilds, it is not as effective as you might hope.

If you just add a new .ndf for the most part only new data will go there, and you may have decreased performance compared to well balanced datafiles.

If you have a TB of data, you probably want more then one .ndf file. There are recommendations based on CPU. If you have 8 CPU you should have 8 datafiles (one .mdf & 7 .ndf)

It is not possible to move all the data off of the .mdf, there is some that must stay there. Also optimally you want to balance files evenly across all your datafiles. Best course IMHO is to move everything you can to a single temp.ndf. Then create your 7 new .ndf files mydatabase_2.ndf, etc and move everything back to the 8 datafiles. This will give you a nicely balanced set of datafiles. It is close to what would have grown naturally if you had all 8 datafiles at the start.

Disk space may be an issue. If you don't have 2TB of room you can't have a mydatabase.mdf and a temp.ndf that are both 1TB. You may have to stop the data movement occasionally so you can trim newly freed space.

1. Create temp.ndf give it a decent autogrow size, probably 500GB (if you have room, make it 1TB from the start)
2. Move data off DBCC SHRINKFILE (N'mydatabase' , EMPTYFILE)
3. Trim free space DBCC SHRINKFILE (N'mydatabase' , 1)
4. When the original .mdf is as small as it will get (about 10MB) create your new files mydatabase_2.ndf, mydatabase_3.ndf, etc
5. Shrink back DBCC SHRINKFILE (N'temp', EMPTYFILE), watch for blocking and space, trim as needed.

While doing the move you will want to watch for blocking and dataspace issues. This is the code I use, run it every few minutes while I am watching.

-- Two queires to provide and idea of how things are going durring a file shrink empty modification on the specific database

-- Look for locks and waits that may be impeding the move in specific database.  
-- !!!!!Be sure to put your database name in the where clause!!!!!!-----------------
Select session_ID
, Start_time
, [Status]
, command
, user_id
, blocking_session_id as 'blocking ID'
, wait_type
, wait_time
--, estimated_completion_time  as 'est comp time'--Values can fluctuate wildly, When it is smaller the cpu_time and decreasing can suddendly finish.  
, cpu_time
, percent_complete as '%conmplete'
, lock_timeout
, deadlock_priority
, last_wait_type
, SDB.name as 'DB_name'
, SDB.state_desc as 'DB_Status'
--, * 

From sys.dm_exec_requests

left join sys.databases as SDB
on sys.dm_exec_requests.database_id = SDB.database_id

where [status] not in ('background','sleeping')
and SDB.name = 'DB_name'-- The database I am working on ---------<<<<<<<<<<<<<<< Change this value------------


--Identifies used space on files, how much data has moved.
select file_id
, type_desc
, name
, substring([physical_name],1,3) AS [Drive]
, physical_name
, state_desc
, size / 128 as 'AllocatedSizeMB'
, FILEPROPERTY([name],'SpaceUsed') /128 AS 'SpaceUsedMB'  --Addapted from https://sqlperformance.com/2014/12/io-subsystem/proactive-sql-server-health-checks-1
, (1- (FILEPROPERTY([name],'SpaceUsed') / CAST (size AS MONEY))) *100 AS 'PercentFree'
, growth / 128 as 'GrowthSettingMB'

 from sys.database_files
 order by type_desc Desc, name