For SQL Server 2005 and later versions, database file storage on compressed drives behaves as follows:
- If your data file belongs to a read-only filegroup, the file is allowed.
- If your data file belongs to a read-only database, the file is allowed.
- If your transaction log file belongs to a read-only database, the file is allowed.
- If you try to bring up a read/write database with files on a compressed drive, SQL Server generates the following error:Msg 5118, Level 16, State 2, Line 1
The file "<file_name>" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
Note This topic also applies to SQL Server 2012 and later versions.
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 interferes with successful recovery.
In Windows Vista and later versions of Windows
Operating system error 665(The requested operation could not be completed due to a file system limitation)
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.)
- The hotfix for Windows Vista and later versions of Windows that's 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.
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:
Article ID: 231347 - Last Review: Aug 11, 2015 - Revision: 1