Managing MDF Files

Alexander Benatti 21 Reputation points
2020-08-21T12:18:32.277+00:00

I have a database MDF file with 800Gb, and the disk is almost full. I'd like to get the best practice to grow create new files to my database.
So, I have some options to do this:

  • Create a new drive, create a new MDF file with autogrowth, and disable autogrowth in the actual MDF file?
  • Create a new drive, create new NDF files, in this case, how to know if the MDF will not increase anymore, Do i have to disable MDF autogrowth?
  • How to migrate user data and indexes to the NDF files?

thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,948 questions
0 comments No comments
{count} votes

Accepted answer
  1. tibor_karaszi@hotmail.com 4,311 Reputation points
    2020-08-21T12:39:42.723+00:00

    You can only have one primary file. SSMS forces the file extension mdf for the primary file.

    The other data files you have are secondary data files. The recommended file extension for such a file is ndf.

    If you add one more file, it will be a secondary file, regardless of what file extension you give it - i.e., no technical different if you name it ndf (the recommended), mdf or doc. So, don't name it mdf, you can probably imagine how confusing that will be to others.

    Just set a max size to the current file and over time storage will be allocated from the other file. And if you rebuild indexes, you will that this happens even quicker.


1 additional answer

Sort by: Most helpful
  1. Ronen Ariely 15,191 Reputation points
    2020-08-21T15:30:56.257+00:00

    Good day,

    SSMS forces the file extension mdf for the primary file.

    This is not accurate. First of all you can use any file name and execute a CREATE DATABASE query from SSMS, but more then this even the SSMS GUI do not force the file extension!

    19350-image.png

    Open the database properties to confirm the files names (you can also open the folder where the file are stored)

    19505-image.png

    19506-image.png

    You can use any name for the files extension! The names mdf, ldf, and ndf are nothing more than a convention.

    you can probably imagine how confusing that will be to others.

    So true :-)

    On April Fools' Day several years ago I mess up the DBA team by creating a database with data file ldf and log file mdf and gave them a task to do. It was very funny to see how people blocked by their conventions :-)

    So, I have some options to do this:

    (1) You can directly move the data file (including the primary file) to a new location in new disk (same with all other files). This is very simple task which include 3 commends: first take the database OFFLINE -> next move the file -> and finely bring the database ONLINE.

    Remember!!! backup all your databases including the system databases before you start anything!

    Do not do anything on production if you do have the knowledge! Always learn and text in non-production environment!

    For more information on moving file check this document:

    https://learn.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases?view=sql-server-ver15

    Tip! do not put your file in the OS disk. Use separate disks for log and data files.

    (2) If your files are well configured, then in most cases you should not move the file but simply add a new file in the new disk. next if needed, you can move specific entities to the new file (for example move specific tables to the new file).

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.