Moving data from the mdf file to the ndf file(s)
SCENARIO:
1)You have an MDF file from a database which has grown up to a size that has almost exhausted the disk space.
2)You now want to keep the MDF from growing further.
3)You also want to transfer the data from the MDF file to the NDF file(s) and shrink the MDF file size.
METHOD 1: (Permanent Solution, may take a long time to complete)
1. This activity comes with a requirement that no users should connect to the database during the maintenance activity.
2. Identify the table(s) that is taking up all the size and see if it has a clustered index (using SQL Server Management Studio )
3. If yes, move the tables in the following manner:
a. Create a new file group and a file (in the new file group) on a different disk with a large initial size, at least twice the size of the data you are moving onto that file.
b. Run the below command
sp_help <table_name> --Identify the Clustered Index_name
-- For SQL Server 2000.
CREATE CLUSTERED INDEX <name of the existing clustered index>
ON <Table_name> (<Column1, Column2,…>)
WITH DROP_EXISTING
ON [<TargetFileName>]
-- For SQL Server 2005 and onwards.
CREATE CLUSTERED INDEX <name of the existing clustered index>
ON <Table_name> (<Column1, Column2,…>)
WITH (DROP_EXISTING=ON)
ON [<TargetFileName>]
4. Shrink that database MDF file , to free the space to the Operating System.
DBCC SHRINKFILE (1, NOTRUNCATE) --This moves allocated pages from the end of the data file to unallocated pages in the front of the file.
DBCC SHRINKFILE (1, TRUNCATEONLY) --Releases all free space at the end of the file to the operating system.
METHOD 2: (Temporary workaround to keep the MDF from growing further)
ü Create about 2 more files in the same file group but different disks with a large initial file size. (if already present , manually increase their file size to a fairly large size)
ü Bigger the size of the other NDF files, more is the available free space; more the free space available in NDFs , lesser will the MDF file grow. (This will be in accordance to Proportional Fill Algorithm.)
ü The word large used above is relative to environments, so we suggest you to do capacity planning for the same. ( Failing which, increase the NDF file sizes to at least about twice the size of the MDF.)
File and Filegroup Fill Strategy
Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.
As soon as all the files in a filegroup are full, the Database Engine automatically expands one file at a time in a round-robin manner to allow for more data, provided that the database is set to grow automatically. For example, a filegroup is made up of three files, all set to automatically grow. When space in all the files in the filegroup is exhausted, only the first file is expanded. When the first file is full and no more data can be written to the filegroup, the second file is expanded. When the second file is full and no more data can be written to the filegroup, the third file is expanded. If the third file becomes full and no more data can be written to the filegroup, the first file is expanded again, and so on.
Please refer the following link for more details:
https://msdn.microsoft.com/en-us/library/ms187087.aspx\
Written By :- Jwalanth BJ Chavan, Support Engineer
Reviewed By:- Sumit Sarabhai,Technical Lead