SQL Server 2014 table compression

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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?