Table Partitioning. What is the correct process for deleting .ndf and .ldf files?

Question

Please go easy it’s a steep learn curve..

OK steps done so far

1. Create files,
2. Create file groups,
3. Create partition function,
4. Create partition scheme,
5. Partition table associate files with filegroups
6. Put data in the table.

All is good

Now to reverse

The drop data

1. Drop the partition and associations,
2. Drop partition scheme,
3. Drop partition function,
4. Drop file groups

But when I deleted the files from the folder in my SSMS, I than had problems validating my Databases.

I restored the files and SSMS is fine.

My question what is the correct process for deleting .ndf and .ldf files?

Answer 1

You can't delete the LDF file: it's essential.

For the NDF, you need to show that it's not used to SQL Server. You'd use DBCC SHRINKFILE with EMPTYFILE. Substitute xxx based on sys.database_files

DBCC SHRINKFILE (xx, EMPTYFILE)