February 13, 2008 at 3:22 am
Hello
I have serveral database using a a certain disk drive, these databases are using a folder called "Database".
I am trying to free some disk space, and I have tried to shrink the db's and the files. I am thinking that if I use NTFS to compress the database folder, will it affect the SQL Server databases themselves?
February 13, 2008 at 3:26 am
SQL Server only supports read-only databases/filegroups on compressed drives.
See in books online under "File Backup and Restore and Compression"
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2008 at 3:51 am
Thanks Gail
It basically means that db's ment for read only purposes can be cmpressed using the NTFS compression feature.
Which also means that it wont be a good idea to use NTFS to compress db's folders which are been used for production or development?
If am wrong kindly correct me, thanks
February 13, 2008 at 4:24 am
b_boy (2/13/2008)
Thanks GailIt basically means that db's ment for read only purposes can be cmpressed using the NTFS compression feature.
It's stronger than that. A DB on a compressed drive must be marked Read-only within SQL, not just intended for mostly read-only access.
Which also means that it wont be a good idea to use NTFS to compress db's folders which are been used for production or development?
It's more than a not good idea. It won't work.
If you try to restore a DB that's not read-only onto a compressed drive, you'll get the following.
Restore failed for Server ' '. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: The file "e:\Db.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed. (Microsoft.SqlServer.Smo)
If you compress a dir where there are existing SQL db files (while the SQL service is shut down) you'll get the following error once SQL comes up and tries to access the DB
Database ' ' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)
If you compress master, the server won't even start.
Ref: http://blogs.msdn.com/sanchan/archive/2006/06/04/617585.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply