Disabling SQL Server Data Compression in TFS Databases


Team Foundation Server has been designed to make use of SQL Enterprise Edition features such as page compression which are not available in other editions of SQL Server. When moving one or more Team Foundation Server databases from an Enterprise Edition of SQL Server to a non-Enterprise Edition of SQL Server (as part of a collection detach/attach operation, for example) it is necessary to disable that compression.

To disable compression on a Team Foundation Server database you can execute [dbo].[prc_EnablePrefixCompression] against it. This stored procedure has a parameter, @online, which should be set to true if you want to disable compression while you continue using the collection database through your Team Foundation Server deployment, but can be set to false otherwise in order to speed up the operation. In either case, the steps to execute this stored procedure will be:

1. Launch SQL Server Management Studio

2. Locate the Team Foundation Server database which will be moved. Right click on the database and select New Query.

3. Type either:

EXEC [dbo].[prc_EnablePrefixCompression] @online = 0, @disable = 1


EXEC [dbo].[prc_EnablePrefixCompression] @online = 1, @disable = 1

Note Depending on whether you plan to continue using the database while disabling compression or not.

4. Run (!Execute) the query and verify success under messages

5. Repeat steps 1 through 4 for all required databases which will be moved.

Disabling compression will require additional disk space. The below query, which can be executed using the same steps as above, will provide you an estimate about the amount of additional disk space that will be required after disabling compression.

select sum(used_page_count) * 8 * 2 /1024.0
from sys.partitions p
join sys.dm_db_partition_stats s
on s.partition_id = p.partition_id
and s.object_id = p.object_id
and s.index_id = p.index_id
where p.data_compression_desc = 'page'

1. The size returned by the above query is in Megabytes(MB).
2. It is advisable to run this query against each Team Foundation Server database before disabling data compression, and then to ensure that enough disk space will be available before actually disabling compression.

More Information

For more information on versions and editions of SQL Server supported by Team Foundation Server see: http://msdn.microsoft.com/en-us/library/dd631889.aspx.

For more information related to SQL Server data compression see: https://technet.microsoft.com/en-us/library/cc280449.aspx.

Article ID: 2712111 - Last Review: 29 Apr 2015 - Revision: 1