November 22, 2017 at 6:59 am
We are using SQL 2014 enterprise and ran a scrip to remove the data compression on one of the DB using the script in the link. We received few alter scripts (90 scripts) from the main script as like below.
ALTER INDEX ALL ON [CodeSense].[tbl_AggregateMap] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
ALTER TABLE [CodeSense].[tbl_AggregateMap] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
Now we are facing disk space issue due to the size of DB. Can anyone let us know how to reset the compression to avoid disk space issue.
November 22, 2017 at 7:13 am
Do you know what level of compression you where using before you removed it? Row or Page?
Where you using the same levels for all indexes and the table of did you mix it?
November 22, 2017 at 9:32 pm
we are particularly not sure of the existing compression level available on the DB. Is there any way to find out the compression level as we have other DBs available.
November 23, 2017 at 1:00 am
If you have a backup of the DB before you removed the compression, you can restore that somewhere and check what indexes were compressed and what compression they had.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 23, 2017 at 1:02 am
If you have a copy of the database you removed compression from somewhere, yes you can query the metadata to find out the compression settings that are in that copy.
If not restore from backup, query the metadata and apply accordingly to your uncompressed copy.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply