Multiple data files for TFS Collection database

Unfortunately Microsoft Team Foundation Server (TFS) creates a collection database with only one data file. Like other products with high activity database like SharePoint the database should have multiple datafiles to optimise for parallel I/O.
Paul Randal has written a fine piece on "Benchmarking: do multiple data files make a difference?" you also should read.

Findings

1111 autogrow events on the datafile in 22 days. This is partly due to the growth rate is 50 MiB.
The data file is about 176 GiB.
Unfortunately I can´t get the file fragmentation as I don´t have access to the OS. But worst case could be the difference between current size and model database size divided by 50 MiB which is about 3600 fragments. Even I have to expand the extra data files in small steps of 512 MiB the final fragmentation on each extra data file will only be about 48 fragments. The total fragmentation after this activity will teoretically be around 830 fragments which is a significant reduction (77 %) in external fragmentation.

General solution

1. Create extra data files (ndf).
2. Move data to extra data files from first data file (mdf).
3. Shrink first data file to equal size of the extra data files.

Sounds easy - but...

• Mind the transaction log. It should not autogrow as a result of this activity. And movement of data pages is registered in the transaction log.
• Data pages are not moved from end of first data file automatically by SQL Server.
• Limited storage...

Testing in a sandbox with sufficient storage showed some challenges.
Looks like target size on extra data files i 24 GiB. With increments in 512 MiB there are about 48 rounds.

The real deal

• Automated translog backup once a hour scheduled and managed by extrenal operator. 48 rounds times one hour gives 48 hours. At least - but on the other hand each round takes only about 10-15 minutes on the given hardware.
• sysadmin. But not local administrator on the Windows Server. Outsourced operations...
• Trace Flag 1117 set default by outsourcing vendor. Otherwise this was a different challenge.

All this considered the activity can be described as two major activities where the second will have several rounds:

1. Create the extra data files (ndf) with small initial size, e.g. 512 MiB. You could disable autogrow on the first data file (mdf) to avoid further unwanted autogrows.
2. Repeat these steps until the first data file (mdf) has equal size compared to the extra data files (ndf).
1. Disable automatic filegrowth on extra data files (ndf). This is to avoid the extra data files (ndf) growing wild and consuming storage out of control while data pages are moved from the first data file (mdf).
2. Move data pages to extra datafiles using DBCC SHRINKFILE (databasename, EMPTYFILE).
3. Enable autogrow on the the extra data files, e.g. 512 MiB.
4. Move allocated data pages in first data file from the end of the file in front of the file using DBCC SHRINKFILE (databasename, NOTRUNCATE).
5. Truncate first data file (mdf) using DBCC SHRINKFILE (databasename, targetsize in MiB).
6. Expand the extra datafiles in a smaller increment than you wanted, e.g. 512 MiB. This step can be omitted first time right after the extra data files (ndf) are created.
7. Check data space in each data file through the DMV sys.database_files or the SSMS report "Disk Usage".
8. Wait for translog backup.
9. Confirm translog backup and free space in translog.

The steps above are described and discussed in detail in the following. I have note created a complete turn-key script as the naming will differ from one TFS installation to another. But you should be able to create your own script by copy-alter the statements.

First - Create extra data files

In this case I add seven extra data files as the database server has more than eight processors (cores). This gives a total of eight data files which I find a fine compromise between multiple data files and simple administration.
I add each extra data file in a separate batch to minimise the impact on the running database installation:
ALTER DATABASE [TFS_SQLADMIN_Collection] ADD FILE (
  NAME = N'TFS_SQLADMIN_Collection_1',
  FILENAME = N'G:\MSSQL\data\TFS_SQLADMIN_Collection_1.ndf',
  SIZE = 512MB , FILEGROWTH = 512MB ) TO FILEGROUP [PRIMARY];
GO
ALTER DATABASE [TFS_SQLADMIN_Collection] ADD FILE (
  NAME = N'TFS_SQLADMIN_Collection_2',
  FILENAME = N'G:\MSSQL\data\TFS_SQLADMIN_Collection_2.ndf',
  SIZE = 512MB , FILEGROWTH = 512MB ) TO FILEGROUP [PRIMARY];
GO
ALTER DATABASE [TFS_SQLADMIN_Collection] ADD FILE (
  NAME = N'TFS_SQLADMIN_Collection_3',
  FILENAME = N'G:\MSSQL\data\TFS_SQLADMIN_Collection_3.ndf',
  SIZE = 512MB , FILEGROWTH = 512MB ) TO FILEGROUP [PRIMARY];
GO
ALTER DATABASE [TFS_SQLADMIN_Collection] ADD FILE (
  NAME = N'TFS_SQLADMIN_Collection_4',
  FILENAME = N'G:\MSSQL\data\TFS_SQLADMIN_Collection_4.ndf',
  SIZE = 512MB , FILEGROWTH = 512MB ) TO FILEGROUP [PRIMARY];
GO
ALTER DATABASE [TFS_SQLADMIN_Collection] ADD FILE (
  NAME = N'TFS_SQLADMIN_Collection_5',
  FILENAME = N'G:\MSSQL\data\TFS_SQLADMIN_Collection_5.ndf',
  SIZE = 512MB , FILEGROWTH = 512MB ) TO FILEGROUP [PRIMARY];
GO
ALTER DATABASE [TFS_SQLADMIN_Collection] ADD FILE (
  NAME = N'TFS_SQLADMIN_Collection_6',
  FILENAME = N'G:\MSSQL\data\TFS_SQLADMIN_Collection_6.ndf',
  SIZE = 512MB , FILEGROWTH = 512MB ) TO FILEGROUP [PRIMARY];
GO
ALTER DATABASE [TFS_SQLADMIN_Collection] ADD FILE (
  NAME = N'TFS_SQLADMIN_Collection_7',
  FILENAME = N'G:\MSSQL\data\TFS_SQLADMIN_Collection_7.ndf',
  SIZE = 512MB , FILEGROWTH = 512MB ) TO FILEGROUP [PRIMARY];
GO

After adding the extra data files the autogrowth is disabled on the first data file (mdf):
ALTER DATABASE [TFS_SQLADMIN_Collection] MODIFY FILE (NAME = N'TFS_SQLADMIN_Collection', FILEGROWTH = 0);

Second - Balance data files

The second activity could be automated, but in my case the server hosts other databases, and maybe some developoers were working late... So I choose to do this manually step-by-step while keeping a sharp eye on the general behaviour of the server.

Filegrowth on extra data files

Automatic filegrowth on the extra data files is disabled during movement of data pages from the first data files (mdf) to the extra data files (ndf). This is to avoid autogrow events and keep the storage usage in control during the entire operation.

If you set alle datafiles, both the first and the newly added, to a zero filegrowth then you can reduce the fragmentation of the files and spare some time by not completely emtying the first datafile.

The filegrowth on the extra data files (ndf) is handled manually during the operation. In this case I ended up with a growth on 512 MiB in each step by several experiments in a sandbox.

The growtrate should be small enough to make it possible to handle the amount of data within one log backup without autogrow events on the transaction log file (ldf). This is again to keep the storage usage in control during the operation.

Move data pages to extra files

Data pages are moved in the database "TFS_SQLADMIN_Collection" from the first data file (mdf) to the extra data files (ndf) with the command
DBCC SHRINKFILE (TFS_SQLADMIN_Collection, EMPTYFILE);
It is the logical name of the data file that is required as the first parameter. Here it is so unfortunately that the database and the first data file has the same name.
The string holding the logical filename is not wrapped in apostrophes like a string usually is in T-SQL.
The parameter EMPTYFILE will move all data pages from the given data file and prepare the file for deletion. In this case I only want the file reduced to a size equal to the other data files. According to the documentation on DBCC SHRINKFILE a ALTER-statement on the data file will remove the emptyfile-flag from the file.

While the data are moved to the extra datafiles you can se a session with your user running the command "DbccFilesCompact". Also you can see heavy I/O activity on all datafiles; reads on the first datafile and writes on the extra datafiles.

Please notice that the data pages moved also are in the transaction log with their data.

Move allocated data pages to front of data file

This can be visualised as a movement of allocated data pages from right to left on a line illustrating the file initialisation to the left.

Truncate data files

When the end of the data file is liberated of allocated data pages, the data file can be truncated down to last extend holding allocated data pages with the command
DBCC SHRINKFILE (TFS_SQLADMIN_Collection, 24576);

The command will truncate the file to 24 MiB, which is my initial qualified guess on the final size of all data files. If the file can't be truncated to this size because the file holds more allocated datapages than the limit then the shrink operation will stop a the last extend holding allocated data pages. But then the command will not throw an error - just finish gracefully as documented.

Now you can set the desired autogrow rate on all the datafiles. The growth rate must be the same on alle the data files.

Expand the extra datafiles

The extra datafiles ndf) are then expanded with the increment described above with a statement like

USE [master]
GO
DECLARE @msg nvarchar(2047) = N'[' + CONVERT(nchar(23), GETUTCDATE(), 126) + 'Z]  Expand extra data files (ndf) to 6 GiB each...';
RAISERROR( @msg, 0, 0);
GO
ALTER DATABASE [TFS_SQLADMIN_Collection] MODIFY FILE ( NAME = N'TFS_SQLADMIN_Collection_1', SIZE = 6GB );
ALTER DATABASE [TFS_SQLADMIN_Collection] MODIFY FILE ( NAME = N'TFS_SQLADMIN_Collection_2', SIZE = 6GB );
ALTER DATABASE [TFS_SQLADMIN_Collection] MODIFY FILE ( NAME = N'TFS_SQLADMIN_Collection_3', SIZE = 6GB );
ALTER DATABASE [TFS_SQLADMIN_Collection] MODIFY FILE ( NAME = N'TFS_SQLADMIN_Collection_4', SIZE = 6GB );
ALTER DATABASE [TFS_SQLADMIN_Collection] MODIFY FILE ( NAME = N'TFS_SQLADMIN_Collection_5', SIZE = 6GB );
ALTER DATABASE [TFS_SQLADMIN_Collection] MODIFY FILE ( NAME = N'TFS_SQLADMIN_Collection_6', SIZE = 6GB );
ALTER DATABASE [TFS_SQLADMIN_Collection] MODIFY FILE ( NAME = N'TFS_SQLADMIN_Collection_7', SIZE = 6GB );
GO
DECLARE @msg nvarchar(2047) = N'[' + CONVERT(nchar(23), GETUTCDATE(), 126) + 'Z] Extra data files (ndf) expanded to 6 GiB each.';
RAISERROR( @msg, 0, 0);
GO
The size can only be defined in integers on the given scale like "GB" or "MB". So the next expansion of the extra data files (ndf) will have the final size defined in a lower scale but a larger number like
ALTER DATABASE [TFS_SQLADMIN_Collection] MODIFY FILE ( NAME = N'TFS_SQLADMIN_Collection_1', SIZE = 6656MB );
as 6656 MiB equals 6.5 GiB.

Before and after I have put a small message with a timestamp. This makes it possible to log the execution and get some durations.
During the entire activity I have copied the output like these messages to a Word document. As the activity is spread over several days I think it is usefull to compile the output and take a look at it when the dust has settled.

As there are several rounds with expansion statements like above I have created a PowerShell script to generate the statements.
function Get-SqlExpandNdf {
  for ([int]$Size = 512; $Size -ile (24*1024); $Size += 512) {
    if ($Size % 1024 -ieq 0) {
      [string]$FileSize = "$($Size/1024)GB"
    }
    else {
      [string]$FileSize = "$($Size)MB"
    }

    [string]$SizeLabel = "$(([System.Math]::Round($Size/1024,1)).ToString().Replace(',','.')) GiB"

    'USE [master];'
    'GO'
    "DECLARE @msg nvarchar(2047) = N'[' + CONVERT(nchar(23), GETUTCDATE(), 126) + 'Z] Expand extra data files (ndf) to $SizeLabel each...';"
    'RAISERROR( @msg, 0,0 );'
    'GO'

    (1..7) |
    ForEach-Object {
      "ALTER DATABASE [TFS_SQLADMIN_Collection] MODIFY FILE ( NAME = N'TFS_SQLADMIN_Collection_$_', SIZE = $FileSize );"
    }

    'GO'
    "DECLARE @msg nvarchar(2047) = N'[' + CONVERT(nchar(23), GETUTCDATE(), 126) + 'Z] Extra data files (ndf) expanded to $SizeLabel each.';"
    'RAISERROR( @msg, 0,0 );'
    'GO'
    ''
  }
}

The output can be streamed to a sql-file with the CmdLet Out-File
Get-SqlExpandNdf | Out-File -LiteralPath 'C:\temp\ExpandNdf.sql' -NoClobber
and added to a general script file for the activity.

Check data space

The data space and the distribution of data on the data files can be seen with a query like this

USE [TFS_SQLADMIN_Collection];
GO
SELECT
  [database_files].[name],
  [database_files].[file_id],
  [database_files].[physical_name],
  ([database_files].[size] * 8.0/1024/1024) AS [size_in_gb],
  (([database_files].[size] * 8.0/1024) - (FILEPROPERTY([database_files].[name], 'SpaceUsed') * 8.0/1024)) AS [free_space_in_mb],
  (([database_files].[size] * 8.0/1024/1024) - (FILEPROPERTY([database_files].[name], 'SpaceUsed') * 8.0/1024/1024)) AS [free_space_in_gb]
FROM [sys].[database_files];
GO
USE [master];
GO
The query will return a result set on six columns and as many rows as there are files to the database inclusive transaction log files (ldf). A result set could look like this

name	file_id	physical_name	size_in_gb	free_space_in_mb	free_space_in_gb
TFS_SQLADMIN_Collection	1	G:\MSSQL\data\TFS_SQLADMIN_Collection.mdf	132.54	22.50	0.02
TFS_SQLADMIN_Collection_log	2	T:\MSSQL\data\TFS_SQLADMIN_Collection_log.ldf	28.78	22171.10	21.65
TFS_SQLADMIN_Collection_1	3	G:\MSSQL\data\TFS_SQLADMIN_Collection_1.ndf	6.50	502.06	0.49
TFS_SQLADMIN_Collection_2	4	G:\MSSQL\data\TFS_SQLADMIN_Collection_2.ndf	6.50	509.56	0.49
TFS_SQLADMIN_Collection_3	5	G:\MSSQL\data\TFS_SQLADMIN_Collection_3.ndf	6.50	509.37	0.49
TFS_SQLADMIN_Collection_4	6	G:\MSSQL\data\TFS_SQLADMIN_Collection_4.ndf	6.50	510.25	0.49
TFS_SQLADMIN_Collection_5	7	G:\MSSQL\data\TFS_SQLADMIN_Collection_5.ndf	6.50	509.62	0.49
TFS_SQLADMIN_Collection_6	8	G:\MSSQL\data\TFS_SQLADMIN_Collection_6.ndf	6.50	510.62	0.49
TFS_SQLADMIN_Collection_7	9	G:\MSSQL\data\TFS_SQLADMIN_Collection_7.ndf	6.50	510.43	0.49

Discussion

This activity should have been held as the TFS Collection database was created. But when this is not the case one might as well get on with it as soon as possible as the technical debt only will increase.

I could have expanded the extra data files in each run just after autogrow was re-enabled on the files, but this order gave me a better script file structure.

I have seen a few recommendations on moving the table "tbl_Content" to a custom filegroup. This filegroup should also be configured with multiple data files. I have not gone further with this configuration as the organisation is only about 1100 employees with around 300 active TFS users and then I figure the TFS collection will not be big enough for this to really matter.