This time I’ll start with the conclusion: it is not a good idea to compress the \Program Files\Microsoft SQL Server\ folder using NTFS compression, not even on a laptop with less than a GB free on the C: partition. Because if you do, you will get an error when installing SP3 for SQL Server 2005. (I know, it’s not a best practice in terms of performance but for once that was really not what I was trying to optimize here. )
When going through the installation steps of the service pack, it will start upgrading your components. And suddenly you get the famous "hotfix.exe has encountered a problem and needs to close" popup. No explanation or any error description, just the usual popup to sent the error report to Microsoft. The application event log gave me more info:
Product: Microsoft SQL Server 2005 — Error 28062. SQL Server Setup cannot install files to the compressed or encrypted folder: C:\Program Files\Microsoft SQL Server\. To continue, make sure that your installation directories are not compressed or encrypted, or specify a different directory, and then run SQL Server Setup again.
The folder properties tell me that I will lose about 1.5 GB when I decompress my SQL Server folder. Luckily I now have 5 GB free (I compressed my whole Program Files folder about a month ago) so that should be okay…
In the meantime the Microsoft SQL Server 2005 Service Pack 3 Setup keeps on running, upgrading the other components. Only the Database Services component is now showing a failure status. When all other components are upgraded, you have the option to click the View Summary button in the Installation Complete screen. This also shows the error mentioned earlier. And of course you could also open the log file (C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log) for details.
Sidenote: my C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\ folder currently contains 1.97 GB of log files. Another folder to clean out once the SP is fully installed.
After decompressing the folder I started setup again. It selected Database Services as the only remaining component for upgrade. This all went smoothly and my SQL 2005 is now happily reporting version 9.00.4035.00.
Microsoft is aware of the issue and said in 2005 that it will consider adding the functionality in a future version. Anyone who wants to try this out on 2008 once the first SP arrives?
If you want to know the inner details of why you should never compress your SQL Server data and log files in a production environment, check out this great article by Ken Henderson.