Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin

EitanBlumin/undo_data_compression.sql

Last active September 3, 2020 00:47
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save EitanBlumin/16005cbee79572040ef6f9025ca4aab4 to your computer and use it in GitHub Desktop.
Save EitanBlumin/16005cbee79572040ef6f9025ca4aab4 to your computer and use it in GitHub Desktop.
Download ZIP
Oh no! You activated data compression but it's not supported on a replica/mirror/log-shipped secondary! Quick, undo it!
Raw
undo_data_compression.sql
SELECT UndoCompressionCommand = N'ALTER INDEX ' + QUOTENAME(ix.name) + ' ON '
+ QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
+ ' REBUILD WITH(DATA_COMPRESSION=NONE,ONLINE=ON,SORT_IN_TEMPDB=ON);'
FROM sys.partitions AS p
INNER JOIN sys.indexes AS ix
ON p.object_id = ix.object_id
AND p.index_id = ix.index_id
AND data_compression <> 0
INNER JOIN sys.tables AS t
ON t.object_id = ix.object_id
WHERE t.is_ms_shipped = 0
AND SCHEMA_NAME(t.schema_id) <> 'sys'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment