Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,506,594 hits
  • Select GETDATE()

    October 2018
    M T W T F S S
    « Apr    
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  
  • 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 »