INF: SQL Server Databases Not Supported on Compressed Volumes

ID: Q231347

The information in this article applies to:

Microsoft SQL Server versions 6.5, 7.0

SUMMARY

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. In addition, performing a SQL Server database backup to disk files on compressed volumes is not recommended.

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.

Performing SQL Server database backups to disk files on compressed volumes is also not recommended. The failure modes are still under investigation, but some evidence indicates that this backup is less reliable than backing up to regular non-compressed volumes. If backups to compressed volumes are performed, a complete DBCC should be run before each backup and after each restore. The time required to run the DBCC should be factored into the decision to use this backup technique.

Additional query words: hard drive drives disks


Keywords          : SSrvAdmin 
Version           : winnt:6.5,7.0
Platform          : winnt 
Issue type        : kbinfo

Last Reviewed: May 18, 1999