April 6, 2016 at 12:05 pm
Hi,
Basically got a server and one of the drives is running out of space.
I can add in new log/db files on another drive ( which has lots of space ) but how can I transfer across the data from the existing ones and remove the files?
Steve.
April 7, 2016 at 3:01 am
Hi I need to do it live? Can I create new db files and move across live?
April 7, 2016 at 3:28 am
If you create the new file in an existing filegroup, this article may help you. If you add the file to a new filegroup, you'll need to move tables manually by rebuilding clustered indexes on the new filegroup. Log files are populated sequentially, so if you add a new log file, you don't need to take any further action.
John
April 8, 2016 at 4:32 am
Hi,
Just so summarise I have currently two drives in use
c: drive - almost full and has the main data file 1.4tb
d: drive - with about 400gb free - a small data file ( same file group )
e: drive - new one with 6tb free
Should I empty the d drive mdf into the c drive mdf and then create another mdf file on the e drive and empty the c drive mdf into the e drive?
April 9, 2016 at 12:41 pm
steve.roberts 86619 (4/6/2016)
Hi,Basically got a server and one of the drives is running out of space.
I can add in new log/db files on another drive ( which has lots of space ) but how can I transfer across the data from the existing ones and remove the files?
Steve.
The primary file in a database, the first data file created, cannot be removed. The supported route is to move the database files, this will incur some down time. See my article at this link[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2016 at 9:28 am
Hi,
i'm ok with keeping the primary data file, just need to create another and transfer it across?
April 11, 2016 at 9:37 am
This script may help in the moving of database files to a new location.
April 11, 2016 at 9:41 am
It all depends on your data and your hardware. Are the drives on separate physical disks? Do you have tables or structures that are frequently accessed at the same time, such as two tables that are often joined? If the answer to both questions is yes, consider creating a new filegroup on a different drive and moving some objects to it, such that when the tables are joined, you reduce contention for disk resource. Another strategy I've seen is to have data (clustered indexes and heaps if you have them) on one filegroup, and (non-clustered) indexes in a separate filegroup on a separate disk.
John
April 11, 2016 at 11:06 am
April 12, 2016 at 7:31 am
Jon.Morisi (4/11/2016)
Move Database Files Without Taking the Database Offline
No good if the table is huge and your storage subsystem is slow. Introduces fragmentation too.
Move the files in a planned downtime window is the best option
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 12, 2016 at 8:37 am
Perry Whittle (4/12/2016)
Jon.Morisi (4/11/2016)
Move Database Files Without Taking the Database OfflineNo good if the table is huge and your storage subsystem is slow. Introduces fragmentation too.
Move the files in a planned downtime window is the best option
The topic user specifically stated, "Hi I need to do it live? Can I create new db files and move across live?"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply