Search
Close this search box.

SQL Server feature that helps you saving disk space

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:
DBName Object Total Records Total Space Used Space (MB) UnUsed Space(MB)
PRODDB dbo.AT_ColumnLog 212768425 63779.73 63504.66 275.08

 

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

DBName Object Total Records Total Space Used Space (MB) UnUsed Space(MB)
PRODDB dbo.AT_ColumnLog 212768425 25934.12 25702.15 231.97

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Hemantgiri Goswami

Hemantgiri Goswami

Throughout my extensive 24-year tenure in the IT industry, I have honed my expertise in SQL Server and cloud technologies. My qualifications include certifications in ITIL, Azure, and Google Cloud, and my professional journey boasts a consistent record of delivering top-notch, dependable, and efficient solutions across diverse clients and domains. In recognition of my dedication and impact, I am honored to have received the Microsoft MVP award for SQL Server on six occasions. Additionally, I actively contribute to various online forums and blogs, acting as a moderator and facilitator of meaningful discussions. My ultimate mission revolves around empowering organizations to enhance the reliability and efficiency of their SQL Server implementations while fostering a culture of continuous learning and growth within the SQL Server community.