I need to come up with the best approach possible to enable page compression on a table 1500 GB in size, with 363,957,740 rows. The database size itself is 1.71 TB and holds archive data.
If I understand correctly, it needs disk space (possibly the same amount, just to be on the safer side) so that it can create a copy of the table with page compression enabled and release the space. This is in
FULL recovery model so, it will be heavily logged.
I have spoken to my capacity planning resource and he agreed to give additional temporary required space for this maintenance, and take back the space once this activity is complete. Having said this, do you think the best approach to be:
- Take a full backup
- Restore the backup onto new temp drives
- Change the recovery model to
- Enable page compression
- Take a full backup after compression and restore original database
- Change recovery model to
Also, instead of enabling page compression. After Step #3, truncate the largest table, enable page compression and then do a
SELECT * INTO ReplicaDB.dbo.ReplicaTbl
Does this enable page compression on existing indexes?
I do not have Test environment to test the above steps. Alternatively, if any better approach is available then please let me know.
The goal is to minimize the future disk space required at its current growth. We are an ERP software company and we have licensed Enterprise Edition. This table is only for archive when some checks are performed and all the data resides in this table. I have 2 indexes (1 CI, 1 NON-CI). None of the columns are
VARCHAR (MAX), they are either