Summary: SQL Server 2005 does not support compressed folders. Don’t use them for database storage or backups.
I had a backup process running at work that would back up my server’s databases into a compressed folder. Everything was fine until my SharePoint database and another database grew larger than 32GB. My backups began to fail with an error that read:
The process cannot access the file because another process has locked a portion of the file.
I was stumped! I contacted a colleague that is a SQL Server expert, and she suggested using FileMon to watch for intruding processes. It may be the anti-virus software requesting the file in the middle of backing up. At that time, I ran my backups manually singling out the two that were failing to backup. I noticed that they would consistently fail at the 32GB mark. That drew my suspicion, and I did some googling to find that Microsoft SQL Server 2005 does NOT support compressed folders!
So, I moved my backups to a regular folder and all is well again. Even my expert colleague did not know of this problem. Luckily, I have read that SQL Server 2008 will support compressed folders.
I hope this information gets grabbed by the web crawlers as finding this information was NOT as easy as it should be.
Here’s another blogger that found this problem a year earlier than me!
http://clay.lenharts.net/blog/2008/01/28/backing-up-to-a-ntfs-compressed-folder/