How to disable SQL Server Enterprise Edition’s data compression in preparation for migrating to a lower version of SQL


This has been probably one of the most common issues I have come across in the past as it relates to Tfs data migration and or upgrade. 

Scenario #1: You may be upgrading Tfs on new hardware and don't have the same version of SQL Server Enterprise in production.
Scenario #2: You have done your proof of concept in a testing environment with a Enterprise edition SQL Server, and now need to move to production where you only have SQL Server Standard Edition

Whichever the scenario, here is a quick solution that MUST be executed prior to the database backup, copy, restore steps.

Run the following command against the SQL Server Enterprise instance.

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

 

Please do not hesitate to share you experience.

Cheers!

Comments (2)

  1. Eric says:

    This is much easier than I thought.  In fact I read a previous blog and found it quite tedious to execute a number of select and update scripts.  I just knew there had to be an easier way.

    I think you should also mention that the user executing this script needs to have elevated access to SQL for example sys admin rights.  All in all, this was a huge time saver for me. Thank you sir!

  2. Ionel says:

    This procedure can be used only on TFS databases.

    If you want to disable copmpression for other DB you have to do something like this:

    SELECT

    SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]

    ,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]

    ,[rows]

    ,[data_compression_desc]

    ,[index_id] as [IndexID_on_Table]

    into #compr

    FROM sys.partitions

    INNER JOIN sys.objects

    ON sys.partitions.object_id = sys.objects.object_id

    WHERE data_compression > 0

    AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'

    while exists(select 1 from #compr)

    begin

          declare @i varchar(200) = (select top 1 [ObjectName] from #compr)

          print @i

          exec ('ALTER INDEX ALL ON '+@i + ' REBUILD WITH (DATA_COMPRESSION = None)')

          delete from #compr where [ObjectName] = @i

    end

    drop table #compr

Skip to main content