Another quick one today:

Rare, but sometimes, the need to remove data files from existing databases does arise. Data files (MDF’s & NDF’s) are not like other files on the filesystem to just delete. They are controlled by Sql Server and need to be configured to be deleted; These operations needs to be executed from inside Sql Server.

Example: Recently on one of our production databases, we added new LUN’s. After adding, our operations people created dummy data files to test the “success of new drives“ by adding data files to an existing production database. I know…operations people do interesting things something.

As these files were created without following the standards we have set for data files, we need to delete them. But its been a few days since they were added. So, Sql Server has already started using them [they were added to existing filegroups].

So, this is what I did.

First, I ran SHRINKFILE command on the dummy file to see how much of the new file actually has data in it (how full is the file).

Once I saw that only a small number of pages actually had any data in them, it seems like emptying the files would not take too much time. The following command helped move the data to other files in the same FILEGROUP.

Once successfully completed, run this command to remove the file from database and OS.

Hope this helps,

_Sqltimes