Rebalance DataFiles in SQL Server

Hi friends! In today’s post we are going to see how to rebalance large datafiles or several smaller ones of equal size. This task optimizes the disks, allowing the information to be distributed evenly and avoiding write necks.

It’s a simple task but that can be prolonged in time due to the movement of pages, both Reading and writing. We start from a stage where neither stop time nor degradation can be estimated so it is recommended to have more time than enough.

The engine itself does not incorporate any tool that automates this task, so we are the ones who have to, through several steps, carry it out. More information in : https://docs.microsoft.com/es-es/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15

HOW CAN WE REBALANCE DATAFILES?

We start from the following stage:

We have the following Data files:

Rebalance DataFiles in SQL Server

We want to leave the 43GB file in 4 11GB files.

The first step will be to create the files we need, in our case as we have commented it would be So. (incidentally we have reduced the log file to 2GB)

Rebalance DataFiles in SQL Server

Since initial file is the master file of the filegroup, we cannot delete it so we have to migrate to 4 new files and then change one of them.

Now we run the shrink with move to new file:

USE [pruebas]
 GO
 DBCC SHRINKFILE (N'tests', EMPTYFILE)
 GO 

*** DBA TIP ***

  • This process can be stopped and relaunched at any time.
  • It does not block objects so it does not produce unavailability.
  • It does not use log space.

Once the process is finished we see that we have the files equally full.

Now it would only be necessary to shrink from 4 to 1 and delete the file.

 USE [pruebas]
 GO
 DBCC SHRINKFILE (N'tests4 ', EMPTYFILE)
 GO 
 ALTER DATABASE[Pruebas] REMOVE FILE [pruebas4] 

As we see, the 4 files now have the same space occupied and will grow evenly increasing performance.

If you need help with this or another operation, do not hesitate to contact us !! Until next time! If you want to see more information about SQL Server do not hesitate to visit: https://www.gpsos.es/category/bases-de-datos/sql-server/

Still do not know Query Performance ? Find out how it can help you in your Oracle environment. More information on their page LinkedIn .

Follow GPS on LinkedIn