Description of support for SQL Server databases on compressed volumes

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

Summary
Microsoft SQL Server databases are not supported on NTFS or FAT compressed volumes except under special circumstances for SQL Server 2005 and later versions. A compressed volume does not guarantee sector-aligned writes, and these are necessary to guarantee transactional recovery under some circumstances.

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.
For more information about exclusions for read-only databases and read-only filegroups in SQL Server 2008, go to the following MSDN website: 

Note This topic also applies to SQL Server 2012 and later versions.
More information
Although it is physically possible to add SQL Server databases on compressed volumes, we do not recommend this, and we do not support it. The underlying reasons for this include the following:
  • Performance

    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 certain scenarios, a SQL Server backup to a compressed volume or compressed folder is not successful. When this issue occurs, you receive one of the following error messages. 

In Windows Vista and later 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)
In earlier versions of Windows

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:
967351 A heavily fragmented file in an NTFS volume may not grow beyond a certain size

Notes
  • 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.
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.
SQL Server requires systems to support "guaranteed delivery to stable media," as outlined in the SQL Server I/O Reliability Program Requirements

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:
967576 Microsoft SQL Server Database Engine Input/Output Requirements
hard drive drives disks disk compression
Properties

Article ID: 231347 - Last Review: 08/11/2015 22:33:00 - Revision: 9.0

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 7.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Express, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Express, Microsoft SQL Server 2008 R2 Express with Advanced Services, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Standard Edition for Small Business, Microsoft SQL Server 2008 R2 Web, Microsoft SQL Server 2008 R2 Workgroup, Microsoft SQL Server 2012 Analysis Services, Microsoft SQL Server 2012 Business Intelligence, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Enterprise Core, Microsoft SQL Server 2012 Express, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Web, Microsoft SQL Server 2014 Business Intelligence, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web, SQL Server 2014 Reporting Services

  • kbsurveynew kbexpertiseadvanced kbsql2005setup kbinfo KB231347
Feedback