Article ID: 2712111
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
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 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.
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
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.