July 30, 2021 at 9:54 pm
I well realize data compression is a subject to a few variables, such as table/index/both and page vs row but in case a certain data compression implementation is desired across all database tables and indexes both, all page, for example, is there a way to set this on a database or even a server level? May be undocumented?
July 31, 2021 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 1, 2021 at 10:41 am
No. There is no way to automatically have compression apply to all tables or indexes in a db. You must specify compression as part of the table/index create.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 2, 2021 at 9:06 am
This was removed by the editor as SPAM
August 5, 2021 at 4:27 am
While you can't set this at a DB or Server level, it's pretty simple to run code across every table to compress it. The basic command is:
ALTER TABLE TableName REBUILD WITH (DATA_COMPRESSION = PAGE);
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 5, 2021 at 3:17 pm
You'd want to make sure PAGE compression was actually appropriate for each specific table first. In certain cases, page compression doesn't do enough good to make it worth SQL's effort to test the compression on each page.
You use a system proc to do that:
EXEC sys.sp_estimate_data_compression_savings 'schema_name', 'table_name', NULL /*or specific index number*/, NULL, 'PAGE' /*or 'ROW' or 'NONE'(to check it w/o compression*/
Sometimes ROW compression is better for a given table.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 12, 2021 at 7:40 am
Nope There is no way to automatically have compression apply to all tables or indexes in a db. but If the database is not in Simple Recovery mode, then you must do a transaction log backup before you can shrink the files. You should have regularly scheduled transaction log backups in between your full database backups to allow for point in time recovery as well as to prevent the transaction log from getting too big mygiftcardsite.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply