Running out of space, add new drive (add file to filegroup)

Question

I've been looking into a bunch of options on how to tackle issue of running out of space

The MDF file is currently on D drive (log file on different drive), I have an new empty E drive that I would like to start using

Based on what i've been told/ read I need to add a file on the E drive, and then run an Alter database command to add this file to the existing file group? Then sql server will automatically see there is a lot of free space on the E drive and start saving data there?

My questions are

1) is the above correct?

2) what are the scripts to run to add the new file to the filegroup

3) do i need to tell sql server to stop saving data to the old D drive (as its almost full)

• Yunus UYANIK: Your guess is kinda correct. You have to add filegroup and file after that you have to set "default" filegroup which is a new one. You can check this out: learn.microsoft.com/en-us/sql/t-sql/statements/…

Answer 1

is the above correct?

Sort of. Adding file and Alter database goes together. Based on what you said I am assuming you only have one filegroup at this time which is called primary.

what are the scripts to run to add the new file to the filegroup

Sample tsql you need to run (copied from books online)

USE master;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
 NAME = Test1dat2,
 FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
 SIZE = 5MB,
 MAXSIZE = 100MB,
 FILEGROWTH = 5MB
);
GO

I highly encourage you to read this:

1. ALTER DATABASE (Transact-SQL) File and Filegroup Options
2. Database Files and Filegroups

do i need to tell sql server to stop saving data to the old D drive (as its almost full)

You do NOT need to tell SQL Server to stop saving data to the old D drive. Because of something called Proportional fill algorithm.

Ref: File and Filegroup Fill Strategy

Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.

Read more about this here.

Answer 2

I would recommend to use spanned volume instead of second file groups. And I would try this as temporary solution before migration to better organized disk space ( raid storage)

• Quade: Thanks, currently we use raid 1 on both these drives (existing and new drive) we were considering raid 10 but it would require a lot of down time. would we able to use spanned volume on both these drives even though they are both raid 1 (4 disks in total over 2 drives)
• kakaz: Creating spanned volume do not require any downtime