Backup to device, UNC file, generates OS error

Question

Backup to device, UNC file, generates OS error

Alocyte

SSChasing Mays

Points: 617
More actions
August 11, 2009 at 2:18 am

#203699

Hi friends, family and fellow SQL dba's
I have a bit of a bother, and it's OS / SQL related. I backup to a UNC device, and frequently get a network related error - but not always. And the error is also OS related...
The scene: 64 bit Windows Server 2008 hosts 64 bit SQL 2008 Standard, and devices are created to point to 64 Server 2008 Server B (\\ServerB\SQL_backups\dvc_DB.bak). The database is backed up daily, on a job, and when run the following error is returned:
Date2009/08/08 06:43:30 AM
LogJob History (Backup_AssetData_2009-08-08)
Step ID3
ServerSQL01
Job NameBackup_AssetData_2009-08-08
Step NameBackup Database - Second Attempt
Duration00:05:33
Sql Severity16
Sql Message ID3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: COMPANY\SERVICE_USER. Processed 94096 pages for database 'AssetData', file 'AssetData' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'AssetData', file 'AssetData_log' on file 1. [SQLSTATE 01000] (Message 4035) The operating system returned the error '64(failed to retrieve text for this error. Reason: 15105)' while attempting 'FlushFileBuffers' on 'dvc_AssetData(\\riscsrv-dcm04\RISCSRV-SQL01_Backups\dvc_AssetData.bak)'. [SQLSTATE 42000] (Error 3634) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The file is generated, the same size as a successful backup, but the job terminates unsuccessful.
I have tried to delete the devices' file, and then the backup usually succeeds. This may point to a permissions' type error, but the user the job is run under is a domain admin. The destination server is not unavailable during this time, and the network shares also remain active throughout the excersize - although I haven't got a way to "prove" this.
I am hoping someone can help me identify the cause of the problem, or has experience with a similar problem which has been resolved.
I have a few databases that backup to the UNC described above (different files) that don't fail... some bigger, some smaller.
My SQL2000 server backs up it's databases to the SQL2008 server, and those backups don't fail with this error.
My research has led me to understand that the 64bit OS tries to buffer the files it receives from another server, and I was wondering if this could be influencing the backups as the destination server is also 64 bit Windows Server 2008.
##20090812## UPDATE
I tried another way to do the backup; with the same results:
Backup database Infovest to disk = '\\riscsrv-dcm04\riscsrv-sql01_backups\dvc_Infovest_Z.bak' with INIT,STATS=1
...
98 percent processed.
99 percent processed.
Processed 3422232 pages for database 'Infovest', file 'InfoVest_Data' on file 1.
100 percent processed.
Processed 14383 pages for database 'Infovest', file 'InfoVest_Log' on file 1.
Msg 3634, Level 16, State 2, Line 1
The operating system returned the error '64(failed to retrieve text for this error. Reason: 15105)' while attempting 'FlushFileBuffers' on '\\riscsrv-dcm04\riscsrv-sql01_backups\dvc_Infovest_Z.bak'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
##Update end##
Thanks,
Habeeb
So long, and thanks for all the fishpaste 😉

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

kgerde Mr or Mrs. 500 Points: 549 More actions · Answer 1

I am having the exact same issue. This is from an SQL 2008 Web Edition fully patched as of today running on Windows 2003 Ent. 64 bit backing up to an identical machine. I have also tried backing up to another identical machine which never had SQL 2008 installed. just to rule out extra issues.

I get the same error as you OS error 64...

I have found this happens more often when the unc path points to a slower write capacity disk but it also seems to happen every time when the disk is raid 5 72 gb 15k sas disks. I have never seen this happen on a RAID 1 pair of the same disks. I have seen it happen more often than not on a SAN which kills my idea that it is due to a disk I/O bottle neck.

Is anyone else seeing this error while you know that the disks in question are still accessible?

I know this isn't an answer however maybe this will help the community find it.

Thanks,

kgerde Mr or Mrs. 500 Points: 549 More actions · Answer 2

I have called Microsoft on this issue and found that the following does work

open regedit

navigate to: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanworkstation\parameters

Create a New DWORD value with the name: SessTimeout

set the value: 360 keep it Hexadecimal

(This value might not work for your backup but it was high enough for mine. If this doesn't work increase the value and try again.)

I hope this helps.

Thanks,

kgerde

Fatherjack SSCommitted Points: 1575 More actions · Answer 3

kgerde - Can you confirm that the registry change is carried out on the backup location server?

Thanks

Jonathan

kgerde Mr or Mrs. 500 Points: 549 More actions · Answer 4

Confirmed I made the change to both machines the backup target and the SQL server. I am going to attempt testing this in a non production system with just the change on the backup target and then just the change on the SQL server to figure out what the minimum required change is.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

If I may, if you have room on the database server to complete your backup locally, I'd do that and THEN move the backup file to the other server. This way if there is a failure on the network or remote server it doesn't cause your backup to fail.

kgerde Mr or Mrs. 500 Points: 549 More actions · Answer 6

I agree 100% with you and I do this every time. However, some people don't have this option and thus I have a job.

kgerde Mr or Mrs. 500 Points: 549 More actions · Answer 7

I made the change only on the target backup server and it seems to work without having to make the change on the SQL server. I could just be getting lucky as I am not able to reliably reproduce the errors.

PiMané SSChampion Points: 13965 More actions · Answer 8

If I may, if you have room on the database server to complete your backup locally, I'd do that and THEN move the backup file to the other server. This way if there is a failure on the network or remote server it doesn't cause your backup to fail.
Lynn Pettis

Couldn't agree more. First do the backup to your local storage, or SAN if you have some, then move it to the other server or tape, whatever you use for backup storage.

It's faster (faster IO on local disks then through network or tape) and safer cause, like Lynn says, it's "network failure" proof..

Pedro

If you need to work better, try working less...

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 9

PiMané (11/12/2009)
If I may, if you have room on the database server to complete your backup locally, I'd do that and THEN move the backup file to the other server. This way if there is a failure on the network or remote server it doesn't cause your backup to fail.
Lynn Pettis
Couldn't agree more. First do the backup to your local storage, or SAN if you have some, then move it to the other server or tape, whatever you use for backup storage.
It's faster (faster IO on local disks then through network or tape) and safer cause, like Lynn says, it's "network failure" proof..
Pedro

Now this I agree with, I hate to back it up locally so I can move it to another disk somewhere else and then to tape.. What a waste, I like to write it to SAN right from the git-go and then it can be swept off to tape from there...

CEWII

James Paul Cooper SSC Rookie Points: 47 More actions · Answer 10

I had this problem. The error, in my case, was due to a lack of permissions on the shared folder on the network computer that I was trying to write to. I had no problem when I gave 'Everyone' 'Full Control.' (which I immediately reset after the test).

I am not quite sure how to go about determining the 'account' to whom to grant permissions.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 11

The backups would probably be executed in the context of the SQL Service account, so whatever account your SQL Server uses to log in to the OS would be the place to start.

CEWII

Glen Joseph SSC Enthusiast Points: 140 More actions · Answer 12

I have experienced this same issue intermittently on few of our servers. I agree with Elliott, this is definitely due to I/O and network bottlenecks.

If you have the luxury of local disk, then backup to it and the move it to SAN. Also, maybe spread your Maintenance Plans schedule, so all the servers are running backups at the same time.

v.sierra SSC Enthusiast Points: 142 More actions · Answer 13

Do this for resolve the problem ...

*but first rename de folder to contains de backups

exec xp_cmdshell 'net use \\MyRute MyPass /user:Mydomine\MyuserDomine'

exec xp_cmdshell 'mkdir \\MyRute\MyNewFolder"

with this you garantee the folder has sufficient privileges to de sql program

Other opcion is change de privilege folder to full privileges to the user SQLServer(INSTANCENAME)User$SERVERNAME$INSTANCE(SERVERNAME) Ejem: SQLServerMSSQLUser$ServerProd$MSSQL(ServerProd)