SQL 2008 Backups to UNC Share Failing 0xC002F210

Question

This problem is driving me NUTS!!

We take backups of all of our production databases to a network share, which are then backed up to tape nightly.

8pm Mon-Fri - Full backup, followed by log backup

7am-7pm Mon-Fri, at half-hour interval - Log backup

Our backups have been working in this manner since we migrated from SQL Server Standard 2000 to 2008, 3 years ago. Recently, the first log backup on Mondays have been failing. Not every time, but almost every time! The rest of the week, we've had no problems. I guess the issue may have something to do with the size of the log backup that's attempted after a weekend of no backups.

Now onto the issue I need a fix for...

All this week, every full backup on our biggest two databases have failed (Both backups < 1GB compressed). There's plenty of disk space on the source and destination servers. I'm guessing the issue is to do with the amount of time it takes to complete the backups of these databases, and/or the size of the backup files required to complete these backups. Changing the backup destination to local storage works fine (and very, very fast in comparison).

From the Job History, I can find a few hints as to what the problem could be...

Code: 0xC002F210 (Always this code, but a mix of the following descriptions...)

"The operating system returned the error '64(failed to retrieve text for this error. Reason: 1815)' while attempting 'SetEndOfFile' on '\drserver\SQLBackups\Database.bak'. BACKUP DATABASE is terminating abnormally.

"The operating system returned the error '64(failed to retrieve text for this error. Reason: 1815)' while attempting 'FlushFileBuffers' on '\drserver\SQLBackups\Database.bak'. BACKUP DATABASE is terminating abnormally.

Please help save my hair and sanity!!

Solution

Error 64 means "The specified network name is no longer available", which is a sort of generic error message relating to network problems. Error 0xC002F210 appears to be a generic error from SQL Server meaning that the job failed. So you see that the problem has a maddeningly vague cause.

A recommendation on Red-Gate's website suggests that you increase (or set) the SessTimeout value in the registry key HKLM\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters to a higher value. 300 is suggested (apparently 45 is the default).

If you change this value, you may have to reboot the client (SQL Server) before the change becomes effective.

Comments

[Matty Brown]

I've added this registry value and finally, I've managed to complete this backup without errors. I'll test this a couple of times before marking this as the answer, but I think you've hit the nail on the head first time!! Thanks :).