SQL Backup on NTFS Compressed folder
I’m starting this topic since last week I had faced a backup issue with compressed drive. In one of our server we have used compressed folder to take backup of our SQL databases. This is working fine and the db is used for OLTP application and it had grown about 73GB. Now we have started facing backup failures only for that database alone all other databases (less than 10GB) are running fine in the maintenance plan. I’ve checked the machine performance and SQL Server is the only process using 50% of CPU and 3GB of memory, other than that the server is not under stress and in fact that box is dedicated to SQL Server.
Our backup has been failed with the below error message and the below event is written to errorlog too.
BackupMedium::ReportIoError: write failure on backup device 'D:\SQLBackup\MyDatabase.bak'. Operating system error 33(The process cannot access the file because another process has locked a portion of the file.).
The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x0000xxxxxxxx in file with handle 0x000000000000xxxx. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it.
Upon running the tests I found IO is so busy with that backup folder. While checking it I found that the system is not able to handle backup and folder compression at the same time when the file size is large, this creates IO timeout for SQL Server and SQL Server in turn returns the general error mentioned above.
As we already know that taking SQL backup to a compressed drive \ folder is not recommended by Microsoft, I’ve removed the compression option for that folder and now the backup ran without errors and it completed within 15 min. So taking a large online SQL backup to a compressed drive is mere waste, where you will spend CPU and more IO for it. Some of my recommendations are below to compress your backup.
-
Take a native SQL Backup to an uncompressed folder and then zip the backup file using any of the zipping (gzip, 7-zip, winzip, etc) utility and delete the SQL Backup file.
-
Buy third party utilities to compress the SQL backup on the fly, i.e taking direct compressed backup, no need to create a separate SQL temporary backup file. You can try LiteSpeed, SQL Backup, Embarcadero etc.
-
Upgrade to SQL Server 2008 Enterprise Edition which supports backup compression.
I hope this information will help others who are facing the same problem.
May 3rd, 2009 - 10:54
Have a look at HyperBac (www.hyperbac.com), developed by original developers of LiteSpeed, runs as a service and uses file system filter compression technology, allows you to backup using native TSQL direct to compressed files (no extended stored procs or third party guis like the other solutions), plus extends compression to BCP, SSIS, DTS and online databases as well as backups
September 2nd, 2009 - 03:45
Vidhya,
Thank you very much for the tip. We had exactly the same problem, and we were pulling our hair.
Once, we removed the “compress” flag, everything is working fine.
Thank you very much
Kris