SQL Server 2014 table compression

asked 2020-08-19T23:21:22.477+00:00
techresearch7777777 1,301 Reputation points

Hello, planning on running a SQL Agent Job to compress some Prod Tables which looks like will take a couple of hours to finish.

Is it correct to say that during this Tables (only) compression job just the target tables will be unavailable/locked and not the entire DB?

If yes will any Views that refer to these Tables also be unavailable/locked?

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,473 questions

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. answered 2020-08-20T06:46:17.687+00:00
    tibor_karaszi@hotmail.com 4,291 Reputation points Microsoft MVP

    The rest of the database is definitely available. I don't recall the details in 2014 and ONLINE options, but I think you can define ONLINE for your REBUILD command meaning even the table itself will be largely available (a short term lock at beginning and end of the operation will be acquired). Views will follow the table, since they are basically "pointers" to the data in the table.

  2. answered 2020-08-20T08:05:25.963+00:00
    Shashank Singh 6,211 Reputation points

    There are two options to compress tables

    1. Online
    2. Offline

    Online table rebuild will not lock entire table but the table whose data you are trying to compress 

    alter table table_name rebuild with (online=on, data_compression=page)

    In this the pages which are compressed would be locked exclusively

    For offline the compression will lock whole table with schema modification lock.

    In both the cases the database altogether will not be locked.

  3. answered 2020-08-20T03:16:20.827+00:00
    CathyJi-MSFT 20,656 Reputation points Microsoft Employee

    Hi techresearch,

    I test this in my environment, during the table compression, the table and the database are all available. But my table compression only use few seconds, suggest you check this in you environment.

    Best regards,
    Cathy
    If the response helped, do "Accept Answer" and upvote it.

  4. answered 2020-08-20T14:20:44.297+00:00
    techresearch7777777 1,301 Reputation points

    Thanks for all of the replies, much appreciated.

    Aiming to compress 2 pretty large tables combined takes about 2 hours to completed.

    One of the commands would be:

    ALTER TABLE [Large_Table_1] REBUILD PARTITION = ALL
    WITH
    (DATA_COMPRESSION = PAGE
    )

    Above command takes 1 hour.

    We have SQL 2014 Enterprise edition is above command default ONLINE or OFFLINE?

    And does running ONLINE take longer or faster in general?

    Regards.

  5. answered 2020-08-20T16:58:00.847+00:00
    techresearch7777777 1,301 Reputation points

    Thanks again for the replies.

    Believe good amount of space is needed when compressing ONLINE.

    If yes then would it be in reference to the DB's Transaction Log file and so estimate around twice it's size?... or if not related to Transaction Log something else?