You are currently offline, waiting for your internet to reconnect

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.0from sys.partitions pjoin sys.dm_db_partition_stats son s.partition_id = p.partition_idand s.object_id = p.object_idand 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:

For more information related to SQL Server data compression see:
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Article ID: 2712111 - Last Review: 04/29/2015 08:35:00 - Revision: 4.0

  • KB2712111