SQL Server 2005 and compressed drives - what you should know?
You will be surprised to know how many times this topic comes up during discussions with SQL Server DBAs.
I guess the reason for confusion or discussion primarily emanates from the fact that the documentation on this topic hasn’t been that detailed. So I thought I will blog about it tonight.
In SQL Server 2000, you could create a new database with its mdf and ldf files located on a NTFS compressed drive. The interface didn’t restrict you from doing that.
However, it is NOT a good idea. Obviously, there is an overhead for compression (during inserts and updates) and decompression (during selects), thereby resulting in slower overall performance.
There is a great blog by Ken Henderson on this topic detailing the ramifications and I would encourage everybody to read it.
https://blogs.msdn.com/khen1234/archive/2005/04/25/411852.aspx
Many DBAs would use compressed file system to save space when using SQL 2000, thereby paying a penalty on performance unknowingly.
With the release of SQL 2005, you can no longer create a read/write database on a compressed file system.
However, read-only file groups in SQL 2005 allow you to store the secondary data files in compressed drives.
I will detail the different scenarios below.
Read/Write Databases in SQL 2005
- 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 drive.
Also, if you try to restore a database backup (.bak) file of a read/write database with its .mdf or .ldf or both files 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.
Read-Only File groups/Read-Only databases in SQL 2005
Read/write database can also have secondary file groups which are read-only.
A read-only secondary file group can host 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 https://msdn2.microsoft.com/en-us/library/ms190257.aspx
IMPORTANT NOTE
You can stop SQL Server service and then compress the data and log files of a read/write database.
However, once you restart SQL Server, the read/write database will still be visible in your Management Studio, but if you try to access it, you will get the following error:
Cannot show requested dialog.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Database '<dbname>' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)
Go back and uncompress the files and everything should go back to normal.
Hope this information helps justify why it is not recommended to host your dat and log files on compressed drives.
UPDATE - Refer to KB 231347 for more information.