January 20, 2013 at 10:21 am
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.
Thank You.
Regards,
Raghavender Chavva
January 20, 2013 at 5:18 pm
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; Theyll drag you down to their level and beat you with experience
January 21, 2013 at 5:42 am
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" 😉
January 21, 2013 at 7:56 am
You are absolutely correct (my bad) :w00t:
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 21, 2013 at 10:04 am
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 ?
Thank You.
Regards,
Raghavender Chavva
January 21, 2013 at 1:56 pm
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" 😉
January 22, 2013 at 11:48 am
I need to move only some tables to other data file which is on same file group.
Not the whole data file.
Thank You.
Regards,
Raghavender Chavva
January 22, 2013 at 1:34 pm
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" 😉
January 22, 2013 at 1:52 pm
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/
---------------------------------------------------------------------
January 22, 2013 at 2:30 pm
Or empty and remove files in the current file group 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2013 at 3:07 pm
OP needs to decide if they want to move particular tables, nonclustered indexes, or just general data to the new drive. 😉
---------------------------------------------------------------------
January 22, 2013 at 3:11 pm
Of course!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply