Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Compress All Objects

By Jakob Bindslet,

This script creates compression statements for all objects in all databases on the instance where the script is run. As a deafult PAGE compression is used. If row compression is desired, it be changed by setting @comp = 'ROW'.

The Script does not run the actual compression commands, this is left up to the user.

The output of the script is a table with five columns:

  • Rows - the number of rows in the object to be compressed.
  • db_name - the name of the database.
  • CompressStatement - the actual statement to be executed if the object should be compressed. The statement tries to performn the operation as an online operation. If this is not possible, the operation is automatically done as an offline operation instead.
  • create_date - the create for the object in question.
  • modify_date - the modify for the object in question.

When the generated compression statements are executed, they print what compression operation was actually executed on what object.

Example:

ONLINE:  [MyDatabase].[dbo].[Clusters];
OFFLINE: [MyDatabase].[dbo].[Nodes];
ONLINE:  [MyDatabase].[dbo].[Instances];
ONLINE:  [MyDatabase].[dbo].[Disks];

Notes:

Compression is a SQL Server 2008+ Enterprise Edition feature.

The script performs compression as online operations, which requires a large amount of free space in the database. In order to minimize the impact of this, the compression statements are generated with the objects with the fewest rows first.

I would recommend running the results of this script with care, and testing it well before production use.

Total article views: 1584 | Views in the last 30 days: 2
 
Related Articles
SCRIPT

Compression of all Objects

This script will compress all objects in the database.

SCRIPT

Scripts for Full DB Compression at partition level

Data compression is a key feature with SQL 2008. This script, can compress all DB objects (partition...

SCRIPT

Script to Compress Tables and Indexes

This script creates scripts to compress all tables and indexes in a database.

BLOG

A Brute Force Way to Compress a Database

There are a handful of scripts out there to compress all of of the objects in your SQL 2008 database...

ARTICLE

A Collaborative Approach To Table Compression

A start at identifying and creating TSQL statements for tables that may benefit from compression.

Tags
compression    
 
Contribute