Wednesday, October 29, 2008

Error: 5118 on MS SQL Server 2005

Are you getting this excepton you attempt to attach a database (.mdf) file using your MS SQL Server 2005 management studio?


Create failed for Database 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ReportServer$SQLSERVER2005.mdf'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ReportServer$SQLSERVER2005.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)


The links below will take you to useful blogs on the causes of this exception. I will not bother to repeat the causes

but give you a quick walk around to resolving.

http://blogs.msdn.com/sanchan/archive/2006/06/04/617585.aspx
http://blogs.msdn.com/sqlblog/archive/2006/10/02/SQL-Server-databases-are-not-supported-on-compressed-volumes.aspx

I hardly found a simple approach to resolving this issue. I used compact/u in my Command Prompt but no joy. Eventually, this is what i try out on my system and it works. See if it will work for you too.

STEPS
1. Go to the file path of the .mdf file. Usually, it is "C:\Program Files\Microsoft SQL Server\MSSQL.1

\MSSQL\Data\ReportServer$SQLSERVER2005.mdf"

2. Right click on the .mdf file and click Properties.

3. When the Properties dialog box opens, click Advanced button

4. On the Advanced Attributes box, uncheck 'Compress contents to save disk space'.

5. Click Ok twice to exit the Properties box.

6. Go through steps 1 - 5 to decompress the log file (.ldf) associated with the .mdf file.

7. Now, try attaching the .mdf file again. It'll work!

No comments: