Turn off SQL Compression

We've never dealt with the SQL Server compression feature professionally, but we got a call yesterday from a consultant that noted that a customer had accidentally turned on compression in a SQL Express server while installing a service pack. At least that's what the customer stated.

 

We didn't even know that compression in SQL Express was possible, or that a service pack would behave like that. 

None the less, the consult found the script below and ran it against the server, and it corrected the problem. 

Use this with the greatest of care. Back everything up. Test, test, test. 

Related Articles

... and you 'll find more on the SQL (General) Menu

From this link:

Downgrade SQL Server database from Enterprise to Standard Edition (objectsharp.com)

SELECT DISTINCT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);'
FROM sys.partitions p
join sys.objects o
on p.object_id = o.object_id
WHERE o.TYPE = 'u'
and data_compression_desc != 'NONE'
UNION
SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);'
FROM sys.partitions p
join sys.objects o
on p.object_id = o.object_id
WHERE o.TYPE = 'u'
and data_compression_desc != 'NONE'

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences