0

I am able to switch out and IN and also truncate partition.But I am not able to delete specific partition file in SQL Server. Suppose there is total 4 partition in order table, I don't need partition 1 where data of 2016 . How can I do it? Is it possible to delete those ndf partition file.

table_name  partition_range         file_groupName  partition_number    number_of_rows
Orders      NULL                    PRIMARY           4                 600956
Orders      2016-12-31 00:00:00.000 orders_2016       1                 200988
Orders      2017-12-31 00:00:00.000 orders_2017       2                 99694
Orders      2018-12-31 00:00:00.000 orders_2018       3                 99334

I have deleted partition using below steps, but not sure whether it is actual process or not:

TRUNCATE TABLE Orders  WITH (PARTITIONS (1));

Alter partition schema which is already created

ALTER PARTITION SCHEME order_by_year_schema NEXT USED orders_2017;

Used to eliminate a boundary point and thus merge the ranges of two partitions.

ALTER PARTITION FUNCTION order_by_year_function() MERGE RANGE ('2016-12-31');

This command moves all data from the specified file to other files.

DBCC SHRINKFILE ('orders_2016', EMPTYFILE);

REMOVE database file

ALTER DATABASE orders REMOVE FILE orders_2023;

REMOVE database filegroup

ALTER DATABASE orders REMOVE FILEGROUP orders_2023;

Need expert opinion and also guide me on this if I am not correct. Thanks in Advanced.

1

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.