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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy