Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

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

Posted by blakhani on May 22, 2014


Recently I was trying to create a database on my SQL Instance using below command

CREATE DATABASE [MyDatabase]
ON   
( NAME = N'MyDatabase', FILENAME = N'E:\BlogContent\DatabaseFiles\MyDatabase.mdf')
LOG ON 
( NAME = N'MyDatabase_log', FILENAME = N'E:\BlogContent\DatabaseFiles\MyDatabase_log.ldf')
GO

But encountered below error

Msg 5118, Level 16, State 1, Line 1
The file "E:\BlogContent\DatabaseFiles\MyDatabase.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Error giving hint about some kind of compression. So if we look at the folder which is specified E:\BlogContent\DatabaseFiles it was in blue color.

image

Blue color actually indicates that folder is compressed. Right Click on folder, go to “Properties” and click on “Advanced…”

image

The checkbox “Compress contents to save disk space” is checked which means compression is enabled. Once we uncheck the box, we should be able to create database.

Here is official stand about compression http://support.microsoft.com/kb/231347/en-us (SQL Server databases are not supported on compressed volumes). There is a great blog by Ken Henderson on this topic detailing the ramifications and I would encourage you to read it http://blogs.msdn.com/khen1234/archive/2005/04/25/411852.aspx 

If we try to restore a database and keep files on compressed folder, same error is raised (in SQL 2014 Management Studio)

TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore of database 'MyDatabase' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The file "E:\BlogContent\DatabaseFiles\MyDatabase.mdf" is compressed but does not reside in a read-only database or filegroup. 
The file must be decompressed. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------

Hope this helps!

 

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    20 Responses to “Solution: The file "MDF" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.”

    1. manu said

      Thanks for sharing.

    2. Thanks for this, saved me a lot of time troubleshooting!

    3. Luis said

      Very useful, thank you very much

    4. Very Good !!

      Thanks for sharing !!

    5. Je3f said

      it works! thanks for sharing!

    6. Rose Maria said

      This is very interesting, You are a very skilled blogger. I have joined your feed and look forward to seeking more of your magnificent post.
      Also, I’ve shared your website in my social networks!

    7. lunarwolfs said

      Thanks. It worked for me

    8. Thanks: that was the problem when I tried to restore a database.

    9. yosef said

      how handle this by c#?

    10. Sadia said

      Thanks it saved my time

    11. satar said

      i don’t know how to express my special thank to You!! all i can say is thank you for your constructive guide!!!! 🙂

    12. Faraz said

      Thanks.

    13. Chirag Mukherjee said

      Awesome Solution. Saved so much time. Highly Appreciated

    14. Thank you my friend, you saved a lot of my time.

    15. ing said

      Thx. This article can solving my problem.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: