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.
For more information related to SQL Server data compression see: https://technet.microsoft.com/en-us/library/cc280449.aspx.
Article ID: 2712111 - Last Review: Apr 29, 2015 - Revision: 1