Microsoft KB Archive/231347

= SQL Server databases not supported on compressed volumes =

Article ID: 231347

Article Last Modified on 11/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Standard X64 Edition
 * Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Enterprise X64 Edition
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Workgroup Edition
 * Microsoft SQL Server 2000 Desktop Engine (Windows)
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q231347



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 ensure transactional recovery in some circumstances.



MORE INFORMATION
While it is physically possible to place SQL Server databases on compressed volumes, it is not recommended and not supported. 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 has been 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 pre-allocated space in database files for rollbacks. It is possible on compressed volumes to get an Out of Space error on pre-allocated 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.

Additional query words: hard drive drives disks disk compression

Keywords: kbinfo kbsql2005setup KB231347

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.