On the earlier mentioned VM, I’ve compressed the entire volume to save some disk space, after creating the database. As I found later though, my SQL Server database wasn’t coming back online. Taking it offline and then bringing it back online with SQL Server Management Studio the reason became apparent – SQL Server does not allow for active database files to be compressed at file system level!
The file "C:\...\database.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
The file "C:\...\database.ldf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
Database 'database' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5118)
And it appears for a good reason – database performance would be affected by the read/write operations on compressed database files and it could happen unknowingly just it happened to me – someone could compress the volume, performance is affected and everyone will be scrambling what might’ve happened to a production database! The compression was pretty good as well, my 8 GB database was only taking about 2.5 GB of space, you can imagine a much larger database saving a lot of space.
Another important reason is database recovery, all explained below:
- SQL Server databases are not supported on compressed volumes
http://blogs.msdn.com/b/sqlblog/archive/2006/10/02/sql-server-databases-are-not-supported-on-compressed-volumes.aspx - SQL Server databases are not supported on compressed volumes
http://support.microsoft.com/kb/231347/en-us
In my case, I kept the volume compressed, but uncompressed my two database files (.mdf, .ldf), problem solved. Interestingly enough though, SQL Server doesn’t seem to apply the same restrictions to tempdb and master databases…
No comments :
Post a Comment