Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database that is close to filling the hard drive that it lives on. I'd like to expand the database on to another hard drive to deal with this.

I see there's a Files option in the database properties under SSMS that I could add to but I don't really want to go adding files without knowing what comes next. Do I need to add a new entry to Filegroups and then add to Files? Is the PRIMARY type only supposed to be used for the first file? If not then what is the difference between making one primary and another secondary? Once I add a file, does the DB engine automatically decide where to put data or do I have to go into my tables and specify this in some way?

Sorry if this is a duplicate. I'd be surprised if it isn't but I couldn't find another question that had these answers.

share|improve this question
add comment

migrated from stackoverflow.com Apr 3 '13 at 14:27

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 5 down vote accepted

Do I need to add a new entry to Filegroups and then add to Files?

You can do one of two things here:

  1. Add a new filegroup and at least one new data file
  2. Add a new data file to an existing filegroup (PRIMARY in this case)

Is the PRIMARY type only supposed to be used for the first file?

No, the PRIMARY filegroup can contain multiple data files.

Once I add a file, does the DB engine automatically decide where to put data or do I have to go into my tables and specify this in some way?

Within the filegroup, SQL Server will manage the data dispersion across the data files through a proportional fill algorithm. Therefore, you will not need to manually administer where the data goes within a filegroup. But, if you create an additional filegroup with one or more data files, and you want data to live on that filegroup then you will need to explicitly state that (unless you set the new filegroup as the default filegroup).

For instance:

create table dbo.YourNewTable
(
    col1 int not null,
    col2 int not null
    -- so on and so forth
) on YourNewFilegroup;
go

To find out what your default filegroup currently is:

select
    name as default_filegroup_name
from sys.filegroups
where is_default = 1;

To change the default filegroup:

alter database YourDatabase
modify filegroup YourNewFilegroup
default;
go
share|improve this answer
    
On your first point, what factors should go in to the decision between making a new filegroup vs just adding one or more files to an existing filegroup? Also, assuming I go with the new filegroup what is the difference between primary and secondary? –  Dean MacGregor Apr 3 '13 at 14:42
    
There are a few considerations here. It is a common practice to put all user objects in a filegroup away from the PRIMARY filegroup. The PRIMARY filegroup will contain the system objects. The reason to keep user objects away from the PRIMARY filegroup is for a couple of reasons: Mainly because the database is online first when the PRIMARY filegroup comes online. And with larger databases, you can massage your DR plan to backup different filegroups, as well as a piecemeal restore. –  Thomas Stringer Apr 3 '13 at 14:46
    
Just be mindful that if you create a new filegroup, you will have to work with the indexes and recreating them to get them on the new filegroup. Typically, when there is a reactive situation (likes yours very well may be) then adding a new filegroup won't be the quickest way to get in the clear. Add a new data file to the existing filegroup, and then with time and a maintenance window on your side you can plan to add a new filegroup and get some (or all) of your data over there. Again, that's a reactive situation. –  Thomas Stringer Apr 3 '13 at 14:49
add comment

In addition to Thomas's answer, you could do it simply using T-SQL. This sample code adds a new file to the Primary file group. In your case I don't really see the need for a new file group. But see Thomas's comment on that.

USE [master]
GO
ALTER DATABASE [Test]
ADD FILE (
    NAME = N'NewDataFile', 
    FILENAME = N'd:\DataR2\Data\NewDataFile.ndf',
    SIZE = 3072KB,
    MAXSIZE = 10485760KB,
    FILEGROWTH = 102400KB
)
TO FILEGROUP [PRIMARY]
GO

Make sure to change the database name, the data file name and it's path and also, very important, the file sizes and increase step to suit your environment.

Places to change:

  • [Test] -> your own db
  • NAME = N'NewDataFile' -> put a meaningful name for the new data file
  • FILENAME = N'd:\DataR2\Data\NewDataFile.ndf' -> physical path and file name
  • sizes -> pick something suitable (eg: a starting size of 200 GB on a 100 GB drive is a bit wrong); for the filegrowth, don't specify 1 MB increments, as it will lead to fragmentation further on.
share|improve this answer
add comment

Not sure if a database can live on 2 different drives.

I see 2 other options for you:

a. Move the database to the new volume as described here

b. Copy all your database files to the new drive and mount it in place of the existing Data folder

share|improve this answer
3  
A DB definitely CAN live on multiple drives - it's a common design pattern to put data and indexes on separate physical drives to speed up access. If you aren't sure you probably should not post an answer ;) –  JNK Apr 3 '13 at 14:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.