Article ID: 231347 - Last Review: February 26, 2009 - Revision: 6.0

SQL Server databases are not supported on compressed volumes

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

This article was previously published under Q231347

Expand all | Collapse all

SUMMARY

Microsoft SQL Server databases are not supported on NTFS or FAT compressed volumes. A compressed volume does not guarantee sector-aligned writes which is needed to guarantee transactional recovery in some circumstances.

Back to the top

MORE INFORMATION

Although it is physically possible to add SQL Server databases on compressed volumes, we do not recommend it, and we do not support it. The underlying reasons for this include the following:

Performance

Databases on compressed volumes may cause significant performance overhead. The amount will vary, depending on volume of I/O and ratio of reads to writes. However, over 500 percent degradation was observed under some conditions.
Database Recovery

Reliable transactional recovery of the database requires sector-aligned writes which compressed volumes do not support. A second issue concerns internal recovery space management. SQL Server internally reserves preallocated space in database files for rollbacks. It is possible on compressed volumes to receive an Out of Space error on preallocated files, interfering with successful recovery.

SQL Server backups to compressed volumes can save disk space. However, they may increase CPU usage during the backup operation. We always recommend that you use the BACKUP checksum facilities to help guarantee data integrity.

For more information about the exclusions for read-only databases and for read-only file groups in SQL Server 2005, visit the following Microsoft Developer Network (MSDN) Web site:

http://msdn.microsoft.com/en-us/library/ms190257(SQL.90).aspx (http://msdn.microsoft.com/en-us/library/ms190257(SQL.90).aspx)

For more information about the exclusions for read-only databases and for read-only file groups in SQL Server 2008, visit the following MSDN Web site:

http://msdn.microsoft.com/en-us/library/ms190257.aspx (http://msdn.microsoft.com/en-us/library/ms190257.aspx)

Back to the top

SQL Server requires systems to support 'guaranteed delivery to stable media' as outlined under the Microsoft SQL Server Always-On Storage Solution Review program. For more information about the input and output requirements for the SQL Server database engine, click the following article number to view the article in the Microsoft Knowledge Base:

967576 (http://support.microsoft.com/kb/967576/ ) Microsoft SQL Server Database Engine Input/Output Requirements

Back to the top