dbatools is a free PowerShell module with over 300 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Invoke-DbaBalanceDataFiles

When you have a large database with a single data file and add another file, SQL Server will only use the new file until it’s about the same size.
You may want to balance the data between all the data files.

The function will check the server version and edition to see if the it allows for online index rebuilds.
If the server does support it, it will try to rebuild the index online.
If the server doesn’t support it, it will rebuild the index offline. Be careful though, this can cause downtime

The tables must have a clustered index to be able to balance out the data.
The function does NOT yet support heaps.

The function will also check if the file groups are subject to balance out.
A file group would have at least have 2 data files and should be writable.
If a table is within such a file group it will be subject for processing. If not the table will be skipped.

Screenshots

dbatools-Invoke-DbaBalanceDataFiles

Examples

This command will distribute the data in database db1 on instance sql1.

Invoke-DbaBalanceDataFiles -SqlInstance sql1 -Database db1

This command will distribute the data in database db1 on instance sql1.

Invoke-DbaBalanceDataFiles -SqlInstance sql1 -Database db1 | Select-Object -ExpandProperty DataFilesEnd

This command will distribute the data for only the tables table1,table2 and table5.

Invoke-DbaBalanceDataFiles -SqlInstance sql1 -Database db1 -Table table1,table2,table5

This command will consider the fact that there might be a SQL Server edition that does not support online rebuilds of indexes.
By supplying this parameter you give permission to do the rebuilds offline if the edition does not support it.

Invoke-DbaBalanceDataFiles -SqlInstance sql1 -Database db1 -RebuildOffline

Author

This command was created by Sander Stad. You can find Sander on Twitter and his blog.

Get-Help

From PowerShell, execute Get-Help Invoke-DbaBalanceDataFiles -Detailed for more information on this function.

Source Code

Want to see the source code? View Invoke-DbaBalanceDataFiles.ps1 on GitHub
 

Related commands