Data_compession = page

SQL Server Administration
,
1

Hi,

i performed a "DATA_COMPESSION" with PAGE level on my DB and i took a full backup with compressed before.
after the process finished, i took a full backup again(with compressed) and the size of the backup grown.( opposite result than I had hoped.)

iIRAN:
ALTER INDEX [IX_INDEX_NAME] ON [dbo].[tableName] REBUILD WITH (DATA_COMPRESSION = PAGE); on INDEX
and ALTER TABLE [dbo].[TableName] REBUILD WITH (DATA_COMPESSION = PAGE); on table.

please Advise me what i'm doing wrong.

Thanks,
S

2

It is to be expected that you would not see any savings in the compressed backup file. I do not know the details of the compression algorithms used in SQL backups, but I suspect it does something similar to what data compression does and beyond. The increased size of the backup is likely due to meta data overhead.

When you apply data compression, your MDF file will have more free space in it (i.e., will use less space) assuming the meta data overhead is less than the space savings because of the data compression.