SQL: Create failed for Database

Problem:-

Today, I’m trying to create a new database in SQL Server 2008 version, it throws the following issue

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

The file "D:\Temp\Test\Test.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)

 

Solution:-

While creating the database using “New Database” wizard, it will be generating the filename in the grid based on the name which we are providing for “database name” textbox as well as available file name in the selected folder.

In my scenario, filename cells are blank in the grid. So I have typed filename with MDF and LDF extension in relevant rows of the grid then I clicked “OK” button to create the database, yes it’s been created.

But still I surprised, why the file name is getting populated the grid. Then I’ve gone thru many options/settings which are available for SQL Server/databases, I couldn’t find any settings.

Again created another database with specifying storage path in different drive, even now also it’s been created.

I verified the path in which I faced the issue in database creation.  I found that the advanced property of the windows folder is enabled with the option “Compress Contents to save to disk space”. Due this “new database” wizard doesn’t have option to generate the filename in “New database” wizard

I unchecked the advanced property “Compress Contents to save to disk space” then I created the problem, it solves my issue.

Refer the below screenshot for further assistance to remove the option “Compress Contents to save to disk space”

1.       Right Click on the folder you want to verify,

2.       Click “Properties”

3.       Click on Advanced Button

4.       Uncheck the option “Compress Contents to save to disk space”

5.       Click on ok button to uncompress the folder structure

Causes:

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

·     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 a similar sort of issue

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

Conclusion:-

Because of performance overhead and database recovery issues it is not a good idea to run SQL Server databases on compressed drives. So while you create/Restore the DB use the drive or folder which is not compressed