SQL Server 2014 table compression

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

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.
12,704 questions
0 comments

7 answers

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

    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.

    1 person found this answer helpful.
    0 comments
  2. Shashank Singh 6,246 Reputation points
    2020-08-20T08:05:25.963+00:00

    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.

    1 person found this answer helpful.
    0 comments
  3. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2020-08-20T03:16:20.827+00:00

    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.

    0 comments
  4. techresearch7777777 1,776 Reputation points
    2020-08-20T14:20:44.297+00:00

    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. techresearch7777777 1,776 Reputation points
    2020-08-20T16:58:00.847+00:00

    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?

    0 comments