How to delete secondary data file by moving it's data to Primary data file

Question

Post reply

How to delete secondary data file by moving it's data to Primary data file

gary1

SSC-Insane

Points: 21152
More actions
July 30, 2010 at 5:45 pm

#221979

Hi,
I have a database with one mdf file and one ldf file. Now I have created a secondary data file .ndf on separate drive and it's been in use for last 1 week
In case, for whatever reasons, if we do not want the secondary data file .ndf and decided to remove it, then how to move the data from secondary data file to Primary data file and then delete the secondary data file?
Is that possible?
Please advice me

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

Hi gmamata,

Check this.

http://technet.microsoft.com/en-us/library/ms189493.aspx

Emptying a file

The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.

SQL

USE AdventureWorks2008R2;

GO

-- Create a data file and assume it contains data.

ALTER DATABASE AdventureWorks2008R2

ADD FILE (

NAME = Test1data,

FILENAME = 'C:\t1data.ndf',

SIZE = 5MB

);

GO

-- Empty the data file.

DBCC SHRINKFILE (Test1data, EMPTYFILE);

GO

-- Remove the data file from the database.

ALTER DATABASE AdventureWorks2008R2

REMOVE FILE Test1data;

GO

Thank You,

Best Regards,

SQLBuddy

gary1 SSC-Insane Points: 21152 More actions · Answer 2

Sqlbuddy,

The Procedure discussed here is emptying the file but where the data is going to?

I need to move the data from Secondary data file to Primary data file..and then delete the secondary file?

thanks

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 3

You can only move data by moving the clustered index (Rebuilding it) on the other filegroup. There is an "ON" clause for specifying the location.

If you have other objects, like stored procs, in the new filegroup, you have to drop and recreate them.

gary1 SSC-Insane Points: 21152 More actions · Answer 4

This is Share point content database and it MUST have only one file group (multiple file groups are NOT supported for Share point content database)

In this case, how can I move the data from secondary data file to primary data file in the same file group and then delete the secondary data file?

Thanks

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 5

Hi gmamata7,

The data will be moved from the secondary file to the primary file withing the same filegroup. Once this is done you can remove the secondary file using the ALTER DATABASE... command as mention in the example.

Thank You,

Best Regards,

SQLBuddy

karthik babu Hall of Fame Points: 3335 More actions · Answer 6

sorry for adding my reply to this old thread.

am using SQL Server 2019 and created a secondary file to fix the disk full error. Unfortunately, the secondary file is created in the PRIMARY filegroup. Now I want to move the secondary file content to the primary file (.MDF) and delete the secondary file. Please let me know, how to do it.

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂

karthik babu Hall of Fame Points: 3335 More actions · Answer 7

Hope i can use this... but still hesitant to use it in production...

USE [dbname]

GO

DBCC SHRINKFILE (N'dbname_Data2' , EMPTYFILE)

GO

______________________________________________________________Every Problem has a Solution; Every Solution has a Problem: 🙂