FuGeRTech

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/

Preface

My current day job is developing SharePoint-based web applications. On Thursday, September 11 (coincidence), the SharePoint site became unresponsive. It was not storing any new data, but viewing existing data was still possible. This was important as I have end-users that rely on reports stored in Document Libraries. The daily reports would not save onto the website.

I’m sure that this article is SQL Server Administration 101 for most folks. For those diving into SharePoint with no DBA, this could be a lifesaver.

Table of Contents

Chapter 1: Transaction Log Deletion – what happens when I lost the WSS_Content transaction log
Chapter 2: The Recovery – the steps to recovering the corrupt MDF file
Chapter 3: Prevention – best practices for database and transaction log backup and truncation

Read more…