March 18, 2013 at 11:24 am
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);
March 18, 2013 at 3:59 pm
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'
.
July 9, 2014 at 7:41 am
maybe this for the size requirement:
WHERE
(convert(varchar,((sys.dm_db_partition_stats.used_page_count*8)/1048576))>10)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply