Setting data compression on a database or even server level possible?

  • 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?

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • This was removed by the editor as SPAM

  • 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);

    • This reply was modified 1 month, 2 weeks ago by  Leo.Miller.

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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.

    • This reply was modified 1 month, 1 week ago by  Dunne15.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply