Moving all pages from one data file to another data file.

When database files are filled completely and there is no space to grow, the common fix for this issue is to follow one of these two options, 1. Add new file to another disk (with more storage), or 2. Manually move the file from one disk to another disk(with more storage). Option 2 requires SQL Server services to be reset and Option 1 does not free up space on main disk, but gives ability to grow the file on second disk.

There is third option too, which lets you move all pages from one file to another file and also you don't need to restart the SQL Service . Also it does not matter even if files are on different disks and then remove the old files where no page left. The process has to be followed is:

1. Add new file in same file group. More Information

2. Move pages from one file to another file. DBCC('LOGICAL_NAME',EMPTYFILE). More Information

3. Run DBCC update usage after above script is completed successfully to refresh the pages and row count. More Information

4. Run sp_helpfile to make sure that the page from where you moved the pages does not have any rows left. More Information

5. If data file from where you moved the pages shows 0 pages and rows left, then you can delete that file from database. More Information