Use the DBCCSHRINKFILEEMPTYFILE option to migrate data

Description of DBCCSHRINKFILEEMPTYFILE: migrate all data in the specified file to other files in the same file group. Because the database engine no longer allows data to be stored in an empty file, you can use the ALTERDATABASE statement to delete the file. Assume that I want to move data from one disk to another.

Description of dbcc shrinkfile emptyfile: migrate all data in the specified file to other files in the same file group. Because the database engine no longer allows data to be stored in an empty file, you can use the ALTERDATABASE statement to delete the file. Assume that I want to move data from one disk to another.

Description of dbcc shrinkfile emptyfile:

Migrate all data in the specified file to other files in the same file group. Because the database engine no longer allows data to be stored in an empty file, you can use the ALTERDATABASE statement to delete the file.

Suppose that I want to move data from one disk to another, but I don't want to leave the database Offline while moving the data. We can use this option. The following is an example:

-- Create demodatabase

Create database test

On primary (name = test, filename = 'd: \ testdata \ test_primary.mdf '),

Filegroup [seconday]

(Name = testsecondary, filename = 'd: \ testdata \ test_secondary.ndf ')

Log on (name = test_log, filename = 'd: \ testdata \ test_log.ldf ')

-- Create tableon secondary filegroup

Use test

Go

Create table test (idint) on [seconday]

-- Insert Demodata

Declare @ int

Set @ int = 0

While @ int <100000

Begin

Insert into testvalues (@ int)

Set @ int = @ int + 1

End

-- Add another dadtafile on secondary file group

Alter database test

Add file

(Name = test_secondary_new, filename = 'd: \ testdata \ test_secondary_new.ndf ')

To filegroup [seconday]

-- Empty oldfile and data will move to another file in the same filegroup

Dbccshrinkfile ('testsecondary ', emptyfile)

Go

-- Show filesizeafter empty file

Dbccshowfilestats

Go

-- Remove old file

Alter database test removefiletestsecondary

-- Drop demodatabase

Use master

Go

Drop database test

This option cannot be used to move System Objects, so it has limitations. In addition, in terms of performance, there is certainly no detach, and then the proximity is faster. The advantage is that the entire database will not be offline.