Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Moving Datab from one datafile to another Expand / Collapse
Author
Message
Posted Sunday, January 20, 2013 10:21 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:23 AM
Points: 732, Visits: 853
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
Post #1409291
Posted Sunday, January 20, 2013 5:18 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,910, Visits: 7,138
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"
Post #1409325
Posted Monday, January 21, 2013 5:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
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"
Post #1409512
Posted Monday, January 21, 2013 7:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:31 AM
Points: 3,910, Visits: 7,138
You are absolutely correct (my bad)

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1409568
Posted Monday, January 21, 2013 10:04 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:23 AM
Points: 732, Visits: 853
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
Post #1409631
Posted Monday, January 21, 2013 1:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
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"
Post #1409705
Posted Tuesday, January 22, 2013 11:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:23 AM
Points: 732, Visits: 853
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
Post #1410201
Posted Tuesday, January 22, 2013 1:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
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"
Post #1410251
Posted Tuesday, January 22, 2013 1:52 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:55 AM
Points: 5,989, Visits: 12,927
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/


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

Post #1410260
Posted Tuesday, January 22, 2013 2:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
Or empty and remove files in the current file group

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1410267
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse