May 7, 2008 at 3:02 am
hi Guys..
i have setup a job for full backup of the database every week. but every week i am getting following error. and u know more over when i run this job again without changing anything it's run successfully. but some time when error appear again then i delete the backup file and take the backup again.. i cann't understand what's going on...
Executed as user: DOMAIN\user. ...database_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1096 pages for database 'datbaseName', file 'datbasase_FullText_Indexes' on file 1.
[SQLSTATE 01000] (Message 4035) Processed 2950 pages for database 'datbaseName', file 'sysft_TextDataCatalog' on file 1.
[SQLSTATE 01000] (Message 4035) Processed 2237 pages for database 'datbaseName', file 'sysft_SearchingCatalog' on file 1.
[SQLSTATE 01000] (Message 4035) Processed 13626 pages for database 'datbaseName', file 'sysft_SearchingeCatalog' on file 1.
[SQLSTATE 01000] (Message 4035) Processed 118 pages for database 'datbaseName', file 'sysft_searh' on file 1.
[SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'datbaseName', file datbaseName_Log on file 1.
[SQLSTATE 01000] (Message 4035) A nonrecoverable I/O error occurred on file "\\10.10.10.10.\ Backup\datbaseName\datbaseNameFullBackup.bak:" 64(error not found).
[SQLSTATE 42000] (Error 3271) BACKUP DATABASE is ter... The step failed.
May 7, 2008 at 9:35 am
ONe question, are you backing up to a network drive. If so, bad choice as any small blip on the network will cause your backup to fail. Try backing up locally and then copying the file over the network.
The SQL Server backup process is not very tolerant of any type of network issue and will fail the backup on all small network glitches (which happen more than we'd like to think).
Marvin Dillard
Senior Consultant
Claraview Inc
July 24, 2008 at 9:03 am
I have the same error with sql server 2000 SP4, w2003 SP2, the back up directory is a local directory.
The database backup(.mdf) or log backup(.ldf) occur the error 3271.
There is no error for the other database in the same maintenance plan
How can i have more informations on this matter???
July 24, 2008 at 2:47 pm
MD (5/7/2008)
ONe question, are you backing up to a network drive. If so, bad choice as any small blip on the network will cause your backup to fail. Try backing up locally and then copying the file over the network.The SQL Server backup process is not very tolerant of any type of network issue and will fail the backup on all small network glitches (which happen more than we'd like to think).
I agree...sounds like there are network glitches at play here.
In addition to backing up locally and then transferring, you might try some utility like ROBOCOPY with the /Z switch to do the transfer...it'll probably be more reliable than using SQL tools.
The Redneck DBA
July 24, 2008 at 3:26 pm
If you get this with a local backup, you have IO subsystem issues.
July 25, 2008 at 6:44 am
The only time I have seen an error message like this, that is one with an IP Address in the file path, is when the backup media was a NAS. It's possible that this drive, the one you believe to be local, is really on a different Server, but mapped in such a way that it appears to be part of the local OS.
Assuming that all the drives are local as stated, I would not expect to see an error message as posted. Did you check to see if there is a Server on the network with the address of 10.10.10.10? Open a command line window on any workstation and run this to find the server.
NSLOOKUP 10.10.10.10
Regards, Irish
May 7, 2009 at 11:36 pm
hi pals,
Is there any other way of fixing this issue other than taking backup in local drive... ....
currently each time this job fails we are deleting all the exisitng backups and starting the job and then it runs fine....
Is there any chance we can fix this issue permenantly... 🙁
May 8, 2009 at 6:52 am
Unfortunately not. It must be direct attached storage or any little glitch in the network will cause your backup to fail. I have used a large capacity USB drive for the "local" backup in the past as it is directly attached, but just a little slow. THe only issue is that the USB drive should be redundant and large enough to hold your backup. Of course if you are investing in a USB drive, you might want to check the price of a internal drive as well. One more thing, remember a production database is only as good as your recovery plan. If management gives a hard time about purchasing more storage remind them of what would happen if the database fails and there is no good backup to restore. That normally seals the deal.
Marvin Dillard
Senior Consultant
Claraview Inc
May 8, 2009 at 7:20 am
MD,
Have you had success with mapping a shared directory on another server?
What we do right now is backup to a local drive, then after the backup completes we run a copy of the file to another Server for redundancy. Additioanlly we have an enterprise backup system that pulls the files to tape (30 day rotation)
I've started working with a SAN. It's expensive, but makes recovery easier.
Regards, Irish
May 8, 2009 at 7:49 am
If this is a regular problem, you need to buy more space. It's that simple.
Mapping shared drives will work...sometimes. However it likely will fail when your database fails, and then you'll be wondering how to explain that all that work was lost.
May 8, 2009 at 9:40 am
Steve,
Let me rephrase my previous post.
By Shared Drive Location, I meant to write backups to not to keep data or transaction logs on. In that case the safer bet is a SAN or adding a storage cage or something along those lines.
sorry I was not clear on that point earlier!
Regards, Irish
May 8, 2009 at 1:56 pm
Jeffrey
I did go that route one time, but was not very comfortable with it. Mapped shared drives still involves a network and I have used them to copy files from the local direct attached storage in the past. As Steve has mentioned, the best solution is to get some local direct storage to receive the backup and then copy move the resulting files once finished. In fact, I have a script that does the local backup, zips the file and then copies it over the network to the device that is backed up by tape. My backup retention is one week local drive, two weeks, network drive, forever on tape as we don't overwrite the tapes here. However, tapes go bad, so I just like to say I maintain a 60 days of backups
Marvin Dillard
Senior Consultant
Claraview Inc
May 8, 2009 at 2:11 pm
Jeffrey
Your plan is sound. However, in the orignal post, they were backing up the file over the network, not to a local drive first. So all posts were tailored for that situation. Backup to local, then copy/move to a shared drive is a sound solution.
Marvin Dillard
Senior Consultant
Claraview Inc
May 8, 2009 at 2:31 pm
For me, redunancy is important, but you are correct that your recovery is only as good as the backup files available. More copies of the file help to prevent loss, but if there is corruption you are in trouble.
We test our backups weekly on a rotation. Meaning that we restore all of the databases from the backups created, but we select a different day each week. In seven weeks time we have tested backups from a different day of the week.
Crazy you say? Perhaps. We just restore the data from production to development database. Helps to kep the data fresh, relatively speaking. We've been fortunate in only having to recover production twice in 6 years.
Regards, Irish
August 27, 2009 at 11:36 am
Let me add something here, hopefully it will help someone in the future.
I had a similar problem. We were backing up to network location through a dedicated network card. I received the same message as Shahzadi followed by this error message on the next backup:
SQL Error 3271: A nonrecoverable I/O error occurred on file…1130(Not enough server storage is available to process this command.
Strange message since there is almost 1 TB of space available on that drive. My system engineer notice the C:\ was running low on disk space. After some cleanup and a few reconfigurations, my backups are running smoothly.
Oh, for disclosure purposes, I agree with the risks of backing up to a network location, this is something I inherited. I am just kicking the can down the road for now. 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply