Partager via

SQL Server databases are not supported on compressed volumes

  • Article

It is important to understand that SQL Server database on compressed volumes are not supported. This blog will help you understand the differences between SQL 2000 and SQL 2005 w.r.t compressed drives.

SQL 2000

In SQL Server 2000, you could create a new database with its .mdf and .ldf files located on NTFS or FAT compressed drives. The interface doesn’t restrict you from doing this.

 

However, it is NOT a good idea and is NOT supported. For more details, refer to KB 231347.

SQL 2005

As opposed to SQL 2000, SQL 2005 doesn’t even allow you to place data and log files of Read/Write databases on compressed volumes. Let’s examine two scenarios.

· Let’s create a database called Compressed_DB with .mdf and .ldf files located on a compressed file system (E drive in my case here). You will get an error:

Create failed for Database 'Compressed_DB'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The file "E:Compressed_DB.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5118)

          This is because a read/write database’s primary .mdf and .ldf files cannot be created on a compressed volumes in SQL 2005.

  • Also, if you try to restore a database backup (.bak) file of a read/write database with its .mdf or .ldf or both pointed on a compressed drive, you will again get an error as follows:

Restore failed for Server '<servername>'. (Microsoft.SqlServer.Smo)

System.Data.SqlClient.SqlError: The file "e:Db.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. (Microsoft.SqlServer.Smo)

  • Log files should never be placed on compressed file systems. System database (master, msdb, model and tempdm) cannot be placed on compressed drives.

A word about Read-Only file-groups/Read-Only databases in SQL 2005

However, there are special scenarios in which SQL 2005 allows you to store the data and log files on compressed drives.

  • If your Read/Write database has read-only secondary file group, then you can host the secondary data files (.ndf) on a compressed drive in SQL 2005.

    This functionality allows you to move historical data between your primary data file (.mdf) over to a secondary data file (.ndf) located on a compressed drive in a read-only file group, thereby saving disk space.

  • If you mark a database read-only, then you can place your primary data file (.mdf) also on a compressed drive.

  • Similarly, restoring a read-only database to a compressed volume is allowed.

For more information on read-only filegroups, refer to the BOL here.

In Conclusion

Because of performance overhead and database recovery issues (as described in KB 231347), it is not a good idea to run SQL Server databases on compressed drives.

Moreover, such installations are NOT supported.

Hope this helps!

POSTED BY : Sanchan Saxena

Comments

  • Anonymous
    October 03, 2006
    What about putting SQL Server in a Microsoft Virtual PC Dynamically Expanding drive. Are those drives considered compressed or should this be ok.
  • Anonymous
    January 26, 2007
    W O R K I N G Solution
  1. Go to a command prompt
  2. Navigate to the folder (here 'E:') the file (here 'E:Compressed_DB.mdf') is situated in
  3. Type 'compact /u' and press enter
  • Anonymous
    September 14, 2007
    W O R K I N G Solution
  1. Go to a command prompt
  2. Navigate to the folder (here 'E:') the file (here 'E:Compressed_DB.mdf') is situated in
  3. Type 'compact /u' and press enter

  • Anonymous
    March 26, 2009
    thanks for this worked a atreat, did have to change my dat and log directory to d:somthing rather then just d but thanks :)

  • Anonymous
    June 18, 2009
    Thanks buddy.... it saved my time ;)

  • Anonymous
    June 30, 2009
    The comment has been removed

  • Anonymous
    March 23, 2012
    Really good piece of information. I was trying to install SQL server to my compressed drive and setup was crashing. Searching for reasons brought me here. Thanks :)

  • Anonymous
    May 12, 2015
    Very useful