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 = 1Note
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.
for other considerations.