Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automate Data Compression Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 11:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:18 AM
Points: 997, Visits: 2,974
I would like to automate the compression job for every week end by filtering on newly created tables/indexes with more than 10gb size, also would like to run on all tables in the server for the first time. Is there a script already on this forum to share, appreacite your help.

Here are the commands i will be using on a SQL Server 2008R2 version, Thanks


ALTER TABLE dbo.tabEMP REBUILD PARTITION = ALL WITH (DATA_COMPRESSION=ROW);
----------------------------------------------------------------------------------------------------
ALTER INDEX [index_main] ON [dbo].[tabEMP] REBUILD WITH (DATA_COMPRESSION=ROW);
Post #1432279
Posted Monday, March 18, 2013 3:59 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
I do not have an automated function, however... This may work until you build one or someone assists with one. Make sure and set results to text.

For tables:

sp_msforeachdb @command1 ='use ?
if ''?'' <> ''master'' and ''?'' <> ''model'' and ''?'' <> ''msdb'' and ''?'' <> ''tempdb''
begin
Select ''USE '' + (Select DB_name())
SELECT DISTINCT ''ALTER TABLE ['' + sys.schemas.name + ''].['' + sys.tables.name+ ''] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE, ONLINE = Off);''
FROM
sys.tables
INNER JOIN sys.partitions ON sys.partitions.object_id = sys.tables.object_id
INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.SCHEMA_ID
select ''GO''
end'

For indexes:

sp_msforeachdb @command1 ='use ?
if ''?'' <> ''master'' and ''?'' <> ''model'' and ''?'' <> ''msdb'' and ''?'' <> ''tempdb''
begin
Select ''USE '' + (Select DB_name())
SELECT DISTINCT ''ALTER INDEX ['' + sys.indexes.name + ''] ON ['' + sys.schemas.name + ''].['' + sys.tables.name+ ''] REBUILD PARTITION = ALL WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = ON, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE )''
FROM
sys.indexes
INNER JOIN sys.tables ON sys.indexes.object_id = sys.tables.object_id
INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
INNER JOIN sys.partitions ON sys.partitions.object_id = sys.indexes.object_id AND sys.partitions.index_id = sys.indexes.index_id
where sys.partitions.data_compression_desc <> ''PAGE''
select ''GO''
END'



.
Post #1432373
Posted Wednesday, July 9, 2014 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 6:32 AM
Points: 1, Visits: 59
maybe this for the size requirement:

WHERE
(convert(varchar,((sys.dm_db_partition_stats.used_page_count*8)/1048576))>10)

Post #1590741
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse