CODEWITHSUNDEEP
sql-servercompression
bobtheguy
bobtheguy

Reputation: 33

Compression for very large tables

I have three very big tables in our production system, I need to do a compression for those tables. I tried to do it by each partitions to save the time because every time I run the compression query will cause the table offline. But it still cost about a day to compress a single partition. Does anyone know a way can help me to compress the tables without cost a long outage time for the system.

ALTER TABLE <table_name> 
REBUILD PARTITION = ALL 
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),
...

)

Upvotes: 1

Views: 2008

Answers (1)

jezza101
jezza101

Reputation: 123

It is possible to compress a table and keep it online, just use ONLINE=ON.

Note, if your table is a heap this will be done single threaded and will take a long time.

ALTER TABLE test_tbl REBUILD WITH (DATA_COMPRESSION=PAGE,ONLINE=ON)

Other options could be to take a copy of the table, compress, then remove the old one and rename the new. If the table is in constant use you would have to do a further synchronisation before the switch. This approach may or may not be possible depending on your setup.

Upvotes: 1

Related Questions