How to disable SQL Server 2008 data compression in a Team Foundation Server 2010 database

Article ID: 2712111
Expand all | Collapse all

Summary

Team Foundation Server 2010 has been designed to make use of SQL 2008\2008 R2 Enterprise Edition only features such as Online index operations, Page compression, Table and index partitioning, Larger read - ahead buffering & Cube perspectives.

If the SQL Server 2008\2008 R2 instance hosting the Team Foundation Server 2010 databases is an Enterprise Edition of SQL Server then the new Team Project Collection creation process would enable SQL compression in the Team Project Collection's database. SQL compression would also be enabled in the Team Foundation Server 2010 configuration database.


To disable SQL compression on a Team Foundation Server 2010 database you can:

1.       Launch SQL Server 2008\2008 R2 Management Studio

2.       Locate each Team Foundation Server 2010 database(Configuration and Collection databases), right click on the database and select new Query

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

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

5.       Repeat steps 1. to 4. for all required databases.



The below SQL query will provide you an estimate about the amount of additional disk space that would be required after compression has been disabled on a Team Foundation Server 2010 database.

selectsum(used_page_count)* 8 * 2 /1024.0
fromsys.partitions p
joinsys.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'  

Note:
1. The size returned by the above sql query is in Megabytes(MB).
2. You should run this query againt each Team Foundation Server 2010 database(Configuration and Collection databases) before disabling data compression.
3. The SQL query should be run in the context of each Team Foundation Server 2010 database seperately.



More information

For more information on versions and editions of SQL Server supported by Team Foundation Server 2010 see: http://msdn.microsoft.com/en-us/library/dd631889.aspx
For more information related to SQL Server 2008 data compression see: http://technet.microsoft.com/en-us/library/cc280449(v=sql.100).aspx
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.

Properties

Article ID: 2712111 - Last Review: April 25, 2013 - Revision: 1.2
Keywords: 
KB2712111

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com