Recently one of our customers has some space issue on one of the production server. This server hosts production CRM database and is a candidate for upgrading CRM to the newer version and later server will be decommissioned. The issue with this server was – a space. Well, you’ve read it correctly! In this era, when storage is much cheaper comparing to what it was priced a few years back, the question is, how much disk a server can accommodate. This is where one of my team members come to a help, he suggested an SQL Server feature that helps you saving disk space. This feature is compression on table or index, introduced with SQL Server 2008.

We ran the script to get details of each table in a database so that we know the candidate table for compression:

Here is what we did as first step, estimating how much space we can reclaim:

EXEC sp_estimate_data_compression_savings 'dbo', 'AT_ColumnLog', NULL, NULL, 'ROW' ;  
GO
object_name	schema_name     	index_id	partition_number	size_with_requested_compression_setting(KB)	size_with_requested_compression_setting(KB)	sample_size_with_current_compression_setting(KB)	sample_size_with_requested_compression_setting(KB)
AT_ColumnLog    	dbo	1	1	48680336	25569464	37224	19552
AT_ColumnLog    	dbo	2	1	16351272	11305320	10888	7528

 SQL Server feature that helps you saving disk space – Compression on Index or table

ALTER INDEX PK_AT_ColumnLog ON
AT_COLUMNLOG
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE,ONLINE=ON);
GO

As I said earlier, this server is old and candidate for decommissioning in near future. The server doesn’t have any alternate way to increase the space needed i.e. expanding a disk size or adding one more disk to it as it’s physical box. This was the case an SQL Server feature that helps you saving disk space and gives you little more lead time to schedule a real move of the server, database when needed.

Browse to troubleshooting