locked
How to perform data compression on all tables and its impact ? RRS feed

  • Question

  • I would like to create maintenance plan which will compress the data from all the tables. Can anyone help me, how to do that? 

    Thanks

    Rahul

    Monday, October 8, 2012 9:20 AM

Answers

All replies

  • Might help you-

    http://msdn.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx

    http://www.mssqltips.com/sqlservertip/1582/implementing-data-compression-in-sql-server-2008/


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Monday, October 8, 2012 9:29 AM
  • Thanks but I am looking for scripts like,

    1) Script to check space occupied by object before data compression
    2) Data compression script for all tables.

    Thanks again..

    Monday, October 8, 2012 12:35 PM
  • Iam not sure about the scripts- might be some one can help here-


    Calculating space savings
    http://www.databasejournal.com/features/mssql/sql-server-data-compression.html

    Enable Compression on a Table or Index
    http://technet.microsoft.com/en-us/library/hh710070.aspx

    sp_estimate_data_compression_savings (Transact-SQL)
    http://technet.microsoft.com/en-us/library/cc280574%28v=sql.100%29.aspx

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/f497b091-6aa2-42a2-8206-ceef1f95e229/


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Monday, October 8, 2012 1:06 PM
  • Hi Randive09,

    To check space occupied by object before data compression, we can use sp_spaceused stored procedure. To compress the database tables, we can use the following command:

    ALTER TABLE <table_name> 
    REBUILD WITH (DATA_COMPRESSION =  <option>)

    For example, we can perform the following command to set all tables compression to PAGE

    EXEC sp_MSforeachtable @command1 = 'alter table ? REBUILD WITH (DATA_COMPRESSION = PAGE);'

    For more detail information, please refer to the following documents and example:

    sp_spaceused (Transact-SQL):
    http://msdn.microsoft.com/en-us/library/ms188776.aspx

    How much space do my SQL Server tables use:
    http://devio.wordpress.com/2007/10/11/how-much-space-do-my-sql-server-tables-use/

    Creating Compressed Tables and Indexes:
    http://msdn.microsoft.com/en-us/library/cc280449(v=sql.105).aspx


    Allen Li

    TechNet Community Support


    Tuesday, October 9, 2012 6:18 AM
  • You can use the script below. Just execute it and copy the output and execute it in another windows to compress all the table. Before executing the script please provide the compression type as None or Page or Row

    DECLARE @Compressiontype VARCHAR(5)
    SET @Compressiontype = 'PAGE' -- Value should be NONE or ROW or PAGE
    SELECT 'ALTER TABLE [' + SCHEMA_NAME (schema_id)+'].['+ name 
    + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+ @Compressiontype + ')'  FROM sys.tables

    In addition to this I wrote a series of article on compression, read it to know the limitations

    http://sql-articles.com/articles/dba/data-compression-sql-server-2008-part-1/
    http://sql-articles.com/articles/dba/data-compression-sql-server-2008-part-2/
    http://sql-articles.com/articles/dba/types-of-data-compression-sql-server-2008-part-3/
    http://sql-articles.com/articles/dba/configuring-data-compression-sql-server-2008-part-4/


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    Tuesday, October 9, 2012 6:42 AM