April 4, 2012 at 12:11 am
Hi,
I have an SQL Server 2008 R2 Backup Maintance plan that has been continuely failing to backup one particular database only. We have been investigating the cause of this for sometime (Error log message haven't been providing very useful feedback) and have discovered that it won't backup if compression is enabled.
The database backups fine with compression off.
I'm curious if any else has experiance this \ has usefull input.
Having this database's backup compressed is extremely desirable as it is a very large file otherwise.
April 4, 2012 at 12:34 am
Try once run directly, not from maintenance plan. Then see error comes or not.
Make sure compression is enabled.
April 4, 2012 at 12:54 am
Post the errors please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2012 at 6:19 pm
Failed: (-1073548784) Executing the query "BACKUP DATABASE [DATABASENAME] TO DISK = N'\\\\path..."
failed with the following error: "The operating system returned the error '87
(failed to retrieve text for this error. Reason: 15105)'
while attempting 'SetEndOfFile' on '\\\\path\\path\\SQLBackups\\path\\path\\path\\path\\path\\database.bak'.
BACKUP DATABASE is terminating abnormally.
Error 87 states: parameter is incorrect.
From my research I couldn't find a reason for an error like this occuring and the general response was recreated the maintenance plan, which fixes the issue in most cases.
However, not in our case. And as soon as backup compression is set to: Do not compress backup, the database backs up fine.
April 4, 2012 at 7:10 pm
Tiazi (4/4/2012)
Failed: (-1073548784) Executing the query "BACKUP DATABASE [DATABASENAME] TO DISK = N'\\\\path..."failed with the following error: "The operating system returned the error '87
(failed to retrieve text for this error. Reason: 15105)'
while attempting 'SetEndOfFile' on '\\\\path\\path\\SQLBackups\\path\\path\\path\\path\\path\\database.bak'.
BACKUP DATABASE is terminating abnormally.
Error 87 states: parameter is incorrect.
From my research I couldn't find a reason for an error like this occuring and the general response was recreated the maintenance plan, which fixes the issue in most cases.
However, not in our case. And as soon as backup compression is set to: Do not compress backup, the database backs up fine.
You are backing up to a network share, correct? Is this on a SAN of some sort? A previous company I worked at had this same problem, it was timing out before the end of file could be reset on the file upon completing the backup. The server allocated more space than was actually used by the backup. I can't remember what they didn't, the storage group was handling the issue. I think they changed a timeout setting, but don't quote me. I will try and contact some from that compay and see if I can get some more definative answers.
April 4, 2012 at 7:11 pm
Also, if you have space on the server, you may want to consider backing up locally then moving it to the network share.
April 4, 2012 at 7:17 pm
Email sent, but I may not hear anything back until tomorrow. I will keep you posted.
April 4, 2012 at 8:13 pm
Correct we are writing it to a DFS.
The avg duration for the job to complete successfully while compression is off is about 5 hours.
When compression is on (and the job fails) it runs for 40 minutes, again only this one database fails, all others backup successfully.
So, I don't think it's a timeout issue. I may test out the moving it upon completion as a possible work around.
April 4, 2012 at 8:18 pm
Tiazi (4/4/2012)
Correct we are writing it to a DFS.The avg duration for the job to complete successfully while compression is off is about 5 hours.
When compression is on (and the job fails) it runs for 40 minutes, again only this one database fails, all others backup successfully.
So, I don't think it's a timeout issue. I may test out the moving it upon completion as a possible work around.
The problem comes when the system doesn't respond in a timely fashion when resetting the end of file on the backup file. SQL Server researves a predetermined amount of disk space for the compressed backup. If the amount of space is more than what is actually used, the system has to reset the end of file when the backup completes. If this takes too long, the backup fails. This is what was happening a previous employer. I have contacted an person who worked on this issue and once I get more information, I will pass that on to you.
April 5, 2012 at 1:09 pm
Here is the email response from my contact at my previous employer. I hope it helps. Be sure to check out the parts I bolded in the response from my contact and your earlier post (copied below).
Lynn,
Good to hear from you! Currently, the Compressed SQL Server backup issue is “fixed”, but via a workaround. The symptoms of the issue are the following errors in the SQL Log when the backup “fails”:
2011-10-27 22:02:55.78 Backup Error: 3634, Severity: 16, State: 2.
2011-10-27 22:02:55.78 Backup The operating system returned the error '87(The parameter is incorrect.)' while attempting 'SetEndOfFile' on '\\SQLBKUP-Prod-2\sqlDbBkup\VCMSSP3U-V1\data\OnlineArchiveDocumentViewer\OnlineArchiveDocumentViewer201110272157_.bak'.
2011-10-27 22:02:55.79 spid105 Error: 3634, Severity: 16, State: 2.
2011-10-27 22:02:55.79 spid105 The operating system returned the error '87(The parameter is incorrect.)' while attempting 'SetEndOfFile' on '\\SQLBKUP-Prod-2\sqlDbBkup\DBINSTANCE\data\SOMEDATABASE\SOMEDATABASE201110272157_.bak'.
2011-10-27 22:02:55.80 Backup Error: 3041, Severity: 16, State: 1.
2011-10-27 22:02:55.80 Backup BACKUP failed to complete the command BACKUP DATABASE OnlineArchiveDocumentViewer. Check the backup application log for detailed messages.
And, FYI – The backup actually completes successfully and it appears that the backup can be restored. It is just that the NAS does not respond within a timeout, so SQL Server is told by the OS that the connection timed out, and SQL Server appropriately assumes the backup failed.
Here are details on why the issue occurs:
1) SQL Server predicts the filesize that will be needed to perform the compressed backup.
2) SQL Server pre-allocates a file of that size on the NAS.
3) SQL Server begins to stream data into the pre-allocated file—compressing the data on the fly.
4) Once the backup is complete, SQL Server knows how much data within the file was actually written.
5) SQL Server tells the NAS to truncate the pre-allocated file to the size determined in Step 4 (this is what is known as a setEndofFile SMB operation).
6) SQL Server waits for the NAS to complete and respond—but if the setEndofFile operation’s duration exceeds the Workstation service ‘SessTimeout’ setting (which defaults to 60 secs), it dies . (This is where the SQL Server Error 87 occurs)
The backup “failures” will occur when the NAS does not respond to step #5 within the 60 second default timeout of the Windows Workstation Service. From the evidence I have seen, it appears that the backup may actually be a valid backup—the backup needs to be tested to see if my theory is correct. The “setEndOfFile” operation is ultimately successful, it just takes longer in some cases than the server is expecting. In some cases this appears to be related to load on the NAS’s disk subsystem and also seems to be related to how much space the SQL Server is asking the NAS to release from the file. In one case I have captured in a network trace the SQL Server is asking the NAS to release 31 GB from a file. There is a lot of write activity accompanying this request—a lot to expect to complete within the 60 second default timeout. Both EMC and Microsoft have sanctioned this registry change (increasing the timeout to 300 seconds) as a way to correct the issue.
Here is some info on the workaround:
http://www.petri.co.il/how-to-disable-smb-2-on-windows-vista-or-server-2008.htm
If you want more info, let me know.
Thanks,
REID
Tiazi (4/4/2012)
Failed: (-1073548784) Executing the query "BACKUP DATABASE [DATABASENAME] TO DISK = N'\\\\path..."failed with the following error: "The operating system returned the error '87
(failed to retrieve text for this error. Reason: 15105)'
while attempting 'SetEndOfFile' on '\\\\path\\path\\SQLBackups\\path\\path\\path\\path\\path\\database.bak'.
BACKUP DATABASE is terminating abnormally.
Error 87 states: parameter is incorrect.
From my research I couldn't find a reason for an error like this occuring and the general response was recreated the maintenance plan, which fixes the issue in most cases.
However, not in our case. And as soon as backup compression is set to: Do not compress backup, the database backs up fine.
April 10, 2012 at 6:21 pm
Thanks heaps Lynn!
This has been extremely useful.
I'm looking into this at the moment and will keep you posted on the outcome.
April 11, 2012 at 5:34 pm
Success!
I didn't follow your exact approach, however the information you provided highlighted something thatI hadn't considered:
- SQL Server pre-allocates a file of that size on the NAS.
- SQL Server begins to stream data into the pre-allocated file—compressing the data on the fly.
So, the solution that've implemented and tested is to before the backup runs, I turn on traceflag 3042 - run the backup and then turn it off.
This sacrifices performance slightly (We found it unnoticable).
By doing this it avoids the situation of timing out when the file size has to be shrunk after compression (from the pre-allocated).
Again, Many thanks Lynn for your help.
April 11, 2012 at 6:22 pm
I'm glad to see that you have a working solution and that I could help.
February 11, 2014 at 7:14 am
Thanks, Lynn!
Thanks, Tiazi!
Thanks, SQL Server Central!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply