Arquivos MDF

Question

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)?

Answer 1

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

Answer 2

I already replied to your other post.

Answer 3

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” .

Answer 4

Hi @Alexander Benatti ,

Is the reply helpful?

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

Answer 5

Hi @Alexander Benatti ,

Is the reply helpful?

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