How To Add Another Disk For a Database Without Interruption On SQL Server

When designing a database, it is necessary to analyze the disk requirement correctly and determine the size of the disks from the beginning.

Sometimes things do not go as we planned (actually most of the time :))

Even if you plan everything very well, sometimes a new application comes up and the free space on your disks will be reduced in a shorter time than you expect.

In such a case;

• If space is available in your storage unit, you can increase the size of your disks online,
• You can move the database to another disk,
• You can solve your problem without any interruption by ensuring that your database continues to grow from another disk.

The topic in this article will be to enlarge the database from another disk.

I would recommend reading the article named “How To Create a Database On SQL Server” to get detailed information about database file group structure and database files.

Everyone thinks that database creation is right click and new database.

But creating a database is actually one of the most important arguments in the database administration.

When you read this article, you will understand better what I mean.

Solution:

In fact, the solution is simple.

We will create new files on the empty disks for the filegroup that the tables are running on.

And we will set the auto growth of the files in the filled disks to none.

Thus, these disks will not be used.

In this way the database will continue to grow from empty disks.

As an example, we right click on the database and select the properties as below and come to the Files tab.

We click on Add files,

1. In the Logical Name section, we give a name to the file .

2. In the FileGroup section, we choose the filegroup we want to grow from the empty disk.

3. We select an initial size for the file from Initial Size. You should not configure it less than 256 MB.

4. In the Autogrow section, select In Megabytes as below and configure it to be at least 256.

5. In the Path field, click on the “…” to select a folder on the blank disk. I usually configure it as a minimum of 6 disks for large databases. So I prefer to create 6 different files for 6 different disks.

6. After adding new files to the filegroup used by the tables, we mark the old files’ auto growt as none. In this way, the database will continue to grow from empty files.