SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Datab from one datafile to another


Moving Datab from one datafile to another

Author
Message
Raghavender Chavva
Raghavender Chavva
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 1280
Hi All,

We have one database with 3TB of size, with 6 Data files and 2 Transactional log files. Due to rapid increment of in data file size free space on the disk is going down. So we need to create one more data file in another disk which is having 2TB of free space and want to move some the data from the old data files to the new data file.

So how to move the tables from one data file to other data file ?
How can we find out which table is on which data file ?

Thanks in Advance.

Thanks and Regards!!

Raghavender Chavva
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6768 Visits: 7394
Here is some code to determine which file group your tables exist on:

SELECT o. [name], o .[type], f .[name],f.data_space_id
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i. data_space_id = f .data_space_id
INNER JOIN sys.all_objects o
ON i. [object_id] = o .[object_id]
WHERE i. data_space_id = f .data_space_id
AND o. type = 'U' AND i. index_id IN (0,1)



Moving the tables from one filegroup to the other is fairly straight forward. Personally I'd just move the entire filgroup to the new drive (rather than moving individual tables). To move the entire file group to a new drive execute the code below, stop the MSSQL Service, physically move the file from one disk to the other, then restart the SQL service:
ALTER DATABASE YourDB MODIFY FILE ( 
NAME = YourDataFileName, FILENAME = 'X:\NewLocation\YourDatFileName.ndf')
GO
ALTER DATABASE YourDB MODIFY FILE (
NAME = YourDataFileNameLog, FILENAME = 'X:\NewLocation\YourDatFileName_log.ldf')
GO


To move just a table itself:
CREATE CLUSTERED INDEX cs_YourTable
ON dbo.YourTable(YourClusteringKeyFields)
WITH DROP_EXISTING
ON [NewFileGroup]



______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20656 Visits: 17244
MyDoggieJessie (1/20/2013)
Personally I'd just move the entire filgroup to the new drive (rather than moving individual tables). To move the entire file group to a new drive execute the code below

Move the files would be best way.


MyDoggieJessie (1/20/2013)
stop the MSSQL Service, physically move the file from one disk to the other, then restart the SQL service

This old chestnut again!!
You do not need to stop the sql server services to physically move a database file. Issue the ALTER DATABASE ... MODIFY FILE command and then take the database Offline, this will allow you to then manipulate the physical OS files. Copy and paste them to the new directory and then bring the database online. Once the database is online successfully, then remove the old file(s).

Keep an eye out for my SSC article on this subject coming soon

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6768 Visits: 7394
You are absolutely correct (my bad) w00t

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Raghavender Chavva
Raghavender Chavva
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 1280
Thank you for your replies.

is this possible, If the both data files on same file group and need to move from one data file to other datafile ?

Thanks and Regards!!

Raghavender Chavva
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20656 Visits: 17244
You can use dbcc shrinkfile passing in the emptyfile parameter, this will empty data from the file to other files in the same file group only. You would then need to remove the file using the ALTER DATABASE command.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Raghavender Chavva
Raghavender Chavva
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 1280
I need to move only some tables to other data file which is on same file group.

Not the whole data file.

Thanks and Regards!!

Raghavender Chavva
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20656 Visits: 17244
Raghavender (1/22/2013)
I need to move only some tables to other data file which is on same file group.

Not the whole data file.

That's not how it works, objects can only be moved between file groups not between files in file groups.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10668 Visits: 13687
sounds like you need to create a new filegroup with its file on the new drive and then move your tables to it by recreating\creating its clustered index.

see -http://www.mssqltips.com/sqlservertip/2442/move-data-between-sql-server-database-filegroups/

---------------------------------------------------------------------
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20656 Visits: 17244
Or empty and remove files in the current file group ;-)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search