Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,435,966 hits
  • Select GETDATE()

    July 2018
    M T W T F S S
    « Apr    
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  
  • Advertisements

Posts Tagged ‘Msg 5118’

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

    Posted in Error | Tagged: , , | 25 Comments »