none
Disable data compression on SQL 2014 for SP 2013 content database RRS feed

  • Question

  • Dear all,

    I want to disable the data compression for granular backup purposes on SQL 2014.

    I found this command, but it only works for TFS databases:

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

    So my question is:

    How to disable data compression on SQL 2014 for a SharePoint 2013 content database


    Thank you very much!

    Best regards,

    Benedikt

    Thursday, June 18, 2015 6:49 AM

Answers

All replies

  • If you want to change the default for the server this shows you how:

    https://technet.microsoft.com/en-us/library/bb933863(v=sql.110).aspx

    Otherwise you can specify if you want to compress a particular backup you can choose to use or not use the compression button in the UI or use the 'NO_COMPRESSION' option.

    https://technet.microsoft.com/en-us/library/ms186865(v=sql.110).aspx

    Thursday, June 18, 2015 8:32 AM
  • After running some queries, I found that only the Objects with the ID 35, 62, 63 have Page Compression. I looked up the Object names, which are the following:

    • syscscolsegments
    • syscsdictionaries
    • syscsrowgroups

    So in order to get the granular backup to work, I guess I have to disable data compression for these objects.

    But I can not find them in the SQL Manager under Tables. They look like system tables, but where can I find them and could I disable data compression on them without causing any harm?

    • Proposed as answer by SQLDBA14 Monday, August 10, 2015 9:23 AM
    Friday, June 19, 2015 10:22 AM
  • Hi Benedikt,

    Did you get a solution for this data compression issue ?

    I have the same problem.

    Thanks.


    Thursday, July 23, 2015 8:22 AM
  • I Have the same issue

    did anyone have the solution?



    • Edited by SQLDBA14 Monday, August 10, 2015 9:46 AM
    Monday, August 10, 2015 9:22 AM
  • I got the reason ,the current NetBackup version supports SharePoint 2013 and SQL 2014 separately, the combination of both is not supported.

    Tuesday, August 18, 2015 6:23 AM
  • I Have the same issue

    did anyone have the solution? 

    Tuesday, January 5, 2016 1:35 PM
  • Hello all,

    I'm not sure I understand your question, but if you want to deactivate compression on all tables and indexes of a database in one run, then this is for you :
    See https://www.pigeonsql.com/single-post/How-to-disable-Data-Compression-on-tables-in-whole-database-in-SQL-Server-2008R2

    It worked great for me on an SQL Server 2014 database for a TFS 2017.3 server that had been migrated from a TFS 2012 server with SQL Server 2012.

    I now want to move it onto Azure DevOps Server then on Azure DevOps Services and I don't want to have to pay for SQL Server Entreprise edition when I can perfectly use a Standard or Express one !!!

    If your question was about Backup compression, this is a completely different subject, I'm sorry but I cannot help you in that case.

    Tuesday, August 13, 2019 9:32 PM