Moving A Table Data Into New Location Within Database

 

 

Problem

I often getting low disk space alert from one of my data drive. Adding space on the disk was not possible because of hard drive size limitations and barriers at OS max disk space size. So I started looking what can I do for database end and I found there is only one table which is having a huge in size. So I decided to move the table's older data to the other disk which I have a good amount of space free there.

Solution

Below are the few major steps for moving table's older data into now disk/location...

Create File group

Add files in File Group

Create Partition Function based on the logic which data you want to move.

Create Partition Scheme 

        Create Table on partition scheme. Here I used clustered index rebuild method to move data.

Step by step implementation

In my case [cz_air] was the table which was having huge in size on the DB and having unique PK on column name air_id.

-- Step 1

/*

get max value for clustered index column for the table. You can put any other value based on your requirement.

for Table cz_air column name is air_id

*/

 select max(air_id) from [dbo].[cz_air]

--2579

 

--step 2

/*

note down all the index info for the table...

*/

 exec sp_helpindex 'cz_air'

/*

index_name    index_description    index_keys

PK_cz_air_New   clustered, unique, primary key located on PS_cz_air      air_id

--Note the table is having only 1 index in mycase.

*/

  

--step 3

/* Create New File Group */

 ALTER DATABASE [air_c9] ADD FILEGROUP [FG_R_SQLdata_MTD]

  

--Step 4

/*

Create new data file pointing to the target location.

*/

 

ALTER DATABASE [air_c9]

ADD FILE

(

NAME='BigTables',

FILENAME = 'R:\SQL_Data\MoveTableData\cz_air.ndf'

)

TO FILEGROUP [FG_R_SQLdata_MTD]

 

--Step 5

/*

Partition Function Creation based on the max value of the clustered column

*/

CREATE PARTITION FUNCTION PF_cz_air_air_id(INT)

AS RANGE LEFT FOR VALUES (2579)

GO 

--Step 6

/*

Partition Scheme creation

*/

CREATE PARTITION SCHEME PS_cz_air

AS PARTITION PF_cz_air_air_id

TO ( [FG_R_SQLdata_MTD], [PRIMARY])

GO

 

--Step 7

/*

Recreate all the indexes on the new Partition Scheme...

In my case, there is only one index. The clustered one. I am lucky. Haha ha.

Once you rebuild the clustered index, all your data going to move into new location. This step can take a while based on size of data and machine's computing power.

*/

CREATE UNIQUE CLUSTERED INDEX PK_cz_air_New

ON cz_air ( air_id ASC )

WITH (DROP_EXISTING = ON)

ON PS_cz_air (air_id)

 

--Step 8 

--Now its time to validate data partition. Below is the script which I have used for the validation.

 SELECT distinct

p.partition_number AS PartitionNumber,

f.name AS PartitionFilegroup,

p.rows AS NumberOfRows

FROM sys.partitions p

JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id

JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id

WHERE OBJECT_NAME(OBJECT_ID) = 'cz_air'

 --Output of the SQL query in my case...