SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Combining two files into one
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dbwilson4
Yak Posting Veteran

50 Posts

Posted - 08/02/2007 :  11:53:47  Show Profile  Reply with Quote
My database has two data files (sql 2005)...how do I combine them into one data file?

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 08/02/2007 :  22:46:43  Show Profile  Reply with Quote
If they are in same file group, you can use 'dbcc shrinkfile' with emptyfile option to merge data. Then remove empty file with 'alter database ... remove file ...'.
Go to Top of Page

dbwilson4
Yak Posting Veteran

50 Posts

Posted - 08/04/2007 :  13:44:56  Show Profile  Reply with Quote
There are in the same filegroup.

Can you help me with the DBCC shrinkfile?

Here's the information: (this is the filegroup)
db1test 224mb
secondarydbtest 222mb
db1test_log 1mb


I want to merge db1test with secondarydbtest and get rid of secondarydbtest
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 08/04/2007 :  20:16:51  Show Profile  Reply with Quote
dbcc shrinkfile ('file_name', emptyfile), you can find details in books online.
Go to Top of Page

bhuvnesh.dogra
Starting Member

India
22 Posts

Posted - 02/05/2010 :  07:42:27  Show Profile  Reply with Quote
We have a SQL 2000 server over here on which we have a large database of 200 GB having 2 datafiles , both in Primarry file group. Datafiles are IT1 (located oin E: drive) nd IT2(located on F: drive). IT1 size is 110 GB & IT2 is newly created data file with 89GB size.

Now we are trying to move all the data from IT1 to IT2 using

DBCC Shrinkfile('IT1',EMPTYFILE) command.


It's completed successfully just in milli secs. that is not the expected time to finish bcoz IT1 is so large . After completition, both files are still of same size means no data movement from IT1 to IT2. What can be reason that command completed prematurely?

For IT2, we have unrestricted growth by 64MB.. please get back to me ASAP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000