Why are Recovery Solution Databases not supported on compressed volumes?
According to Microsoft (Microsoft KB article 231347), 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. Here is an excerpt:
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:
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.
For more detailed information please see Microsoft KB Article 231347.
Rate this Article