Avatar of Gordon Hughes
Gordon Hughes
Flag for United Kingdom of Great Britain and Northern Ireland asked on

How to combine two data files

I have a database which has 2 mdf data files, called MP2DATALIVE.mdf and MP2DATA2LIVE.mdf
I would like to only have 1 data file called MP2DATALIVE.mdf
How do I combine these data files
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Gordon Hughes

8/22/2022 - Mon
julian_brunt

Depends on the data within.
I would sugest you need to create some query to extract data from each and insert into the new file.  Again all very ddependant on the structure of data and queries within the files.
jogos

If that second file is of primay filegroup

To get logical names
select name, physical_name
from sys.database_file



DBCC SHRINKFILE (logicalname_file2, EMPTYFILE)
http://technet.microsoft.com/en-us/library/ms189493.aspx
ALTER DATABASE REMOVE ligicalfilename2
http://msdn.microsoft.com/en-us/library/bb522469.aspx
Gordon Hughes

ASKER
Hi
Bothe files are shown as in the primary group
Not sure how I run the
select name, physical_name
from sys.database_file
which datbase do I run this against the database name is MP2Live
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
jogos

use MP2Live
select name, physical_name 
from sys.database_file

Open in new window

Gordon Hughes

ASKER
Hi

I did that and it comes up with did not recognise sys.database_file

Any ideas
jogos

Sorry, it's sys.database_files with an S  

http://msdn.microsoft.com/en-us/library/ms174397(v=sql.90).aspx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gordon Hughes

ASKER
Hi jogos

OK got the names, see attachment
What do I do next?
Gordon
Logical-names.jpg
jogos

First run a full backup of your database (as before any big change)


DBCC SHRINKFILE (MP2DATALIVE2, EMPTYFILE)
ALTER DATABASE REMOVE MP2DATALIVE2
Gordon Hughes

ASKER
Hi jogos
I have done a back up
The tried to run your statement against the MP2LIVE db, got the following:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'MP2DATALIVE2'.

Any suggestions
Gordon
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
jogos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gordon Hughes

ASKER
Hi jogos
OK tried that and got this messaqge

Msg 2556, Level 16, State 1, Line 1
There is insufficient space in the filegroup to complete the emptyfile operation.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FILE'.

Any suggestions
Gordon
jogos

Can your other file grow?
Gordon Hughes

ASKER
Hi jogos

How do I check and change that
Gordon
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jogos

Gordon Hughes

ASKER
Hi gogos

Thanks for you support and patience
The system is on a vitual drive so have extended to volume to get loads of space
Now trying your script again
Gordon
Gordon Hughes

ASKER
Hi jogos

Ok, increased drive space and found the MP2DATLIVE file was set to maximum value, so changed to unlimited
ran it and got this message

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FILE'.

I still have two data files, but don't know if thye system is using both

Any suggestions where I go from here
Gordon
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Gordon Hughes

ASKER
Hi jogos

Should the script has some ()
to read like this DBCC SHRINKFILE (MP2DATALIVE2, EMPTYFILE)
GO
ALTER DATABASE REMOVE FILE (MP2DATALIVE2)
go
Gordon Hughes

ASKER
Hi jogos

I have run a parse on both versions of the script and get the following

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FILE'.

Gordon
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jogos

Ok see you have found it yourself
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gordon Hughes

ASKER
OK
I will restore the database from the backup and then run it again
Should work OK
Will let you know
Gordon
Gordon Hughes

ASKER
Excellent Support