Share via

Arquivos MDF

Alexander Benatti 21 Reputation points
2020-08-21T11:46:46.687+00:00

Bom dia, tenho um banco de dados sendo o arquivo com 800Gb, e o disco onde ele está armazenado está praticamente cheio. Gostaria de saber qual é a melhor pratica para isso.

Criar um novo drive, criar um novo MDF com autogrow e o arquivo atual desabilitar o autogrow?

Criar um novo drive, e criar arquivos NDF, neste caso, como saber que o MDF não crescerá mais, devo desabilitar o autogrow?

Os dados atuais em MDF serão migrados para o(s) NDF(s) criado(s)?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,870 questions

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alexander Benatti 21 Reputation points
    2020-08-21T12:04:42.083+00:00

    English
    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

    0 comments
  2. tibor_karaszi@hotmail.com 4,301 Reputation points
    2020-08-21T12:40:47.357+00:00

    I already replied to your other post.

    0 comments
  3. m 4,271 Reputation points
    2020-08-24T03:27:35.42+00:00

    Hi @Alexander Benatti ,

    Create a new drive, create a new MDF file with autogrowth, and disable autogrowth in the actual MDF file?

    Create a new drive, create a new MDF file with autogrowth

    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?

    You can check the disk report to get the increase details. And of course there are many ways to get the result, please reference : https://www.sqlshack.com/get-details-of-sql-server-database-growth-and-shrink-events/

    • How to migrate user data and indexes to the NDF files?

    You do not need to migrate the indexes specially, because the data store style as next: SQL Server will distribute the new data to all data files in all spaces according to the size of the existing free space of all files in the same file group. If a file is full, SQL Server no longer writes to this file, but writes to other files with space.

    Note: For a busy database, the recommended setting is to enable the automatic database growth option to prevent the application from running out of space and cause the application to fail, but it is necessary to strictly avoid automatic growth. At the same time, try not to use the auto shrink function.

    More information: https://learn.microsoft.com/en-us/sql/relational-databases/policy-based-management/place-data-and-log-files-on-separate-drives?view=sql-server-ver15, https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15

    BR,
    Mia
    If the reply is helped, please do “Accept Answer” .

    0 comments
  4. m 4,271 Reputation points
    2020-08-25T01:18:37.773+00:00

    Hi @Alexander Benatti ,

    Is the reply helpful?

    BR,
    Mia
    If the reply is helped,please do "Accept Answer".--Mia

    0 comments
  5. m 4,271 Reputation points
    2020-08-26T01:21:54.48+00:00

    Hi @Alexander Benatti ,

    Is the reply helpful?

    BR,
    Mia
    If the reply is helped,please do "Accept Answer".--Mia

    0 comments