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.
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:
Databases on compressed volumes may cause significant performance overhead. The amount will vary, depending on the volume of I/O and on the 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, and compressed volumes do not support this scenario. 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, and this interfers with successful recovery.
In certain scenarios, a SQL Server backup is not successful to a compressed volume or compressed folder. When this issue occurs, you receive one of the following error messages. On Windows Vista and on later versions of Windows
On earlier versions of Windows
STATUS_FILE_SYSTEM_LIMITATION The requested operation could not be completed due to a file system limitation
Operating system error 665(The requested operation could not be completed due to a file system limitation)
STATUS_INSUFFICIENT_RESOURCES insufficient system resources exist to complete the requested service
Operating system error 1450(Insufficient system resources exist to complete the requested or 33(The process cannot access the file because another process has locked a portion of the file.)
For more information about this issue, see the following article in the Microsoft Knowledge Base:
A heavily fragmented file in an NTFS volume may not grow beyond a certain size
- The hotfix for Windows Vista and for later versions of Windows that is discussed in KB article 967351 may not resolve the issue of SQL Server backups that are not successful to a compressed volume or to a compressed folder. However, this hotfix will help mediate the issue.
- After you apply the hotfix that is discussed in KB article 967351, you must format the drive on which compression is enabled by using the /L parameter. When you format the drive on which compression is enabled by using the /L parameter, the Bytes Per File Records Segment increases from 1,024 bytes to 4,096 bytes.
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:
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:
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:
Microsoft SQL Server Database Engine Input/Output Requirements