Moving Database Files to New Disk Deive

Question

Hello,
We have Sql Server 2008R2 and one of the database is more then 2 TB and we can't add any more space on current disk drive so adding new disk drive on the server what will be the best option to move files to new location?

Is it better to move all the files to new drive?
Is it just add another new data files to new disk drive will be better so new data will load directly into new drive location?
Detach/attach?
Backup/Restore?

I am just trying find the best way to avoid or minimize down time as we are at the stage that current disk drive is almost full and can't expand the space but adding another disk drive will make which way better solution?

Currently, We have data file structure like data1.mdf, data2.ndf, data3.ndf, data4.ndf and log file is log1.ldf - all are in PRIMARY GROUP.

Any better solution/suggestion appreciated.

Thanks for your help!

Answer 1

Thanks Erland.
Currently minimum downtime needed for 2+ TB DB and other important application is running.
So you mean turning of Auto Growth all existing Data files will be better?

So I will add New disk into server and add more data files into new drive.

Is it when data get loaded then it will load proportionally into all the data files then existing data files are almost full so it will complain that files are near full or full? Is it ok for long run?

Answer 2

Thanks once again Erland.
Agreed that file should be same size and filling up in parallels but in my case if I add few more files and keep the same size so any new data can be loaded mostly as eventually existing files will be filled up so it will try to filling up new files in parallel.
I am not thinking to move log file (currently only one log file) right now as if I follow above process then I don't have to take database offline.

What's your thought?

Answer 3

Thanks Erland.

It's a BIG question that adding new file I should better take Database in single user mode or just adding online it's fine as my datafiles are not all the totally full right now, it has some space?

Anyone has thoughts regarding adding file either single user or multiuser mode fine?

Answer 4

Thanks Erland

Answer 5

Hello,
I am planning to use right now above suggested step so I will be creating two data files on a new drive called G:\ which will be having 1TB space or may be more.
My current DB size is 2TB+
currently my files are as per following size:

FILE GROUP_NAME	name	physical_name 	Size in MB / Initial Size	max_size	Auto Growth	File Name  
PRIMARY	AdWorksData1	D:\DATA1\AdWorksData1.mdf	400220	-1	By 1024 MB - Unrestricted	AdWorksData1.mdf  
PRIMARY	AdWorksData2	D:\DATA2\AdWorksData2.ndf	393908	-1	By 1024 MB - Unrestricted	AdWorksData2.ndf  
PRIMARY	AdWorksData3	D:\DATA3\AdWorksData3.ndf	398000	-1	By 1024 MB - Unrestricted	AdWorksData3.ndf  
PRIMARY	AdWorksData4	D:\DATA4\AdWorksData4.ndf	399260	-1	By 1024 MB - Unrestricted	AdWorksData4.ndf  
  

Following script is right or any size I should be adjust?
USE master;
GO
ALTER DATABASE AdWorks
ADD FILE
(
NAME = AdWorksData5,
FILENAME = 'G:\DATA5\AdWorksData5.ndf',
SIZE = 1024MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024MB
),
(
NAME = AdWorksData6,
FILENAME = 'G:\DATA6\AdWorksData6.ndf',
SIZE = 1024MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024MB
)
TO FILEGROUP PRIMARY;
GO