Ms Sql Server Error 5118
★★★★★★★★★★★★★★★ SQL Server Support ContentOctober 2, 20068 Share 0 0 It is important to understand that SQL Server database on compressed volumes are not supported. This blog will help you understand the differences between SQL 2000 and SQL 2005 w.r.t compressed drives. SQL 2000
In SQL Server 2000, you could create a new database with its .mdf and .ldf files located on NTFS or FAT compressed drives. The interface doesn’t restrict you from doing this. However, it is NOT a good idea and is NOT supported. For more details, refer to KB 231347. SQL 2005 As opposed to SQL 2000, SQL 2005 doesn’t even allow you to place data and log files of Read/Write databases on compressed volumes. Let’s examine two scenarios. · Let’s create a database called Compressed_DB with .mdf and .ldf files located on a compressed file system (E drive in my case here). You will get an error: Create failed for Database ‘Compressed_DB'. (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) The file "E:Compressed_DB.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) This is because a read/write database’s primary .mdf and .ldf files cannot be created on a compressed volumes in SQL 2005. 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 an error as follows: Restore failed for Server ‘SQL Server experts to answer whatever question you can come http://www.sqlteam.com/forums/topic.asp?topic_id=128032 up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums. SQL Server Forums http://blog-mstechnology.blogspot.com/2012/01/sql-create-failed-for-database.html Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL ms sql Server 2005 Forums Other SQL Server Topics (2005) Attach/Detach Database Confusion Reply to Topic Printer Friendly Author Topic kashif27 Starting Member Pakistan 7 Posts Posted-06/22/2009: 15:05:17 Hi . . . . .I created a database in SQL Server 2005 on one machine. On that machine the database was stored in ms sql server the default "....\Microsoft SQL Server\MSSQL.1\MSSQL\Data" folder. After that I detached the DB and copied it to another machine. On that machine, initially I stored the database file at location other than the default db file location of sql server. When I tried to attach the db file, it gave me teh following error.TITLE: Microsoft SQL Server Management Studio------------------------------Attach database failed for Server 'UZ'. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476------------------------------ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------The file "G:\..........\abc.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.Could not open new database 'Dotnet67'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 5118)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=5118&LinkId=20476------------------------------BUTTONS:OK------------------------------Then I copied the db file on this new machine in the default sql server db file folder "....\Microsoft SQL Server\MSSQL.1\MSSQL\Data" and then when I attached the db, it was attached.So though my problem is solved, but I do not understand
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 becreated 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