Failing Backups and FlushFileBuffers

SQL Server can be a finicky beast. Powerful, robust, ACID-compliant — yes, of course, we all love SQL Server, but it can be a bit sensitive.

Once upon a time, my database backups were failing, and perhaps yours are too. The following message in SQL Server’s error log was my only clue:

Executing the query "BACKUP DATABASE <database> TO <database>..." failed with the following error: "The operating system returned the error '64(The specified network name is no longer available.)' while attempting 'FlushFileBuffers' on '<database>(<backup location>)'. BACKUP DATABASE is terminating abnormally.

I love Microsoft error messages. They’re somehow vague yet specific at the same time, which requires quite a bit of skill in my opinion.

So what does any IT professional do when presented with such a message?

Ask Google! Google that error message and you’ll come across site after site recommending the following solution…

According to Microsoft article KB102067, the solution requires the creation or modification of a DWORD registry key with the name Sesstimeout in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters on the database server in question. I chose a value of 300. Why 300? Well, Microsoft sets the default value of Sesstimeout to 45, as in 45 seconds. This proved to be insufficient, as evidenced by our current conundrum. Setting a value of 300, as in 300 seconds, would increase session timeout to 5 minutes. If your session is timing out for longer than 5 minutes, you’ve got bigger problems than registry keys.

Simple minds or those in emergency situations may stop here, but inquiring individuals may go the extra mile and search for a root cause. What could be causing session timeouts?

This most certainly points to a resource issue — system, SAN, network… overutilization, misconfiguration, discombobulation… you name it. Digging deeper into each of these is too much for one blog post, but perhaps it’s just enough to send you on your journey to a better, more reliable environment. Good luck and Godspeed.