Solved

SQL 2000 Transactional Backup Failures.

Posted on 2013-01-03
9
567 Views
Last Modified: 2013-01-09
Hey Guys,

After doing as much research as I can online I'm currently stuck. Plus my knowledge of sql 2000 is smaller then a grain of salt.

Just recently I have started to receive this error from our sql 2000 box.
JOB RUN:      'Transaction Log Backup Job for DB Maintenance Plan 'DataConnect'' was run on 1/3/2013 at 11:35:00 AM
DURATION:      0 hours, 28 minutes, 40 seconds
STATUS: Failed
MESSAGES:      The job failed. The Job was invoked by Schedule 40 (Schedule 1). The last step to run was step 1 (Step 1).


I look at the history and the log file and this is what I find:
Message
Executed as user: SVAMAIN\sqlservice. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

I then go to the SQL Server logs and view the current log.
At the same time when I received the email that the job failed I am able to find this:
Source: spid69
BackupDiskFile::RequestDurableMedia: failure on backup device \\xxxxx. Operating system error 64(The specified network name is no longer available.).

When I go to the location of where the backup file is there are transactional logs there (whether they are complete or not is another question but data was written to the network locations).

Things have been going fine until today. Nothing on the network has changed. A few months back I had to move the backups from our SAN to our NAS due to storage issues, migrations, etc. The hop count hasn't changed and it's the same millsecond response time when doing a ping and tracert.

From all the research I can see is that SQL 2000 really doesn't like it when the network connection get's congested and slows down. Instead of continuing to keep running it fails the job. Is there a way to force the Maintenance plan to keep going regardless of network congestion or slow response times from the destination NIC?


Thank you for any help that you can provide :)
0
Comment
Question by:HckrAdm2005
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 38741696
How is the NAS connected? We noticed this internally on some USB-connected drives that newer version of Windows disconnects these on occasion. As soon as you access the drives, they work; therefore, data eventually writes, but it causes errors initially.

Anyway, if your question is how to get the job to continue on error, change the step's on failure procedure. If you expand SQL Server Agent > Jobs, right-click maintenance job, and select Properties, then you will be able to edit the individual Steps. Under the advanced options for each step, you will see "On failure action" which you can change to "Go to next step." This will ignore the error and continue process the job. You will also see options to set "Retry attempts" and the associated "Retry interval (minutes)."

I hope that helps!

Best regards and happy coding,

Kevin
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38742721
are you using sql 2000 native backup? if 3rd  party like the idera sqlsafe software there is an option write try for such network issues..
in any case check if your \\xxxxx server is busy that time: try to change bkp schedule or just set in job retry on failure , add more cpu on \\xxxxx ; faster hdd; nic , etc
0
 
LVL 1

Author Comment

by:HckrAdm2005
ID: 38744762
Hey Kevin,

The NAS is connected via 1GB Ethernet. However there is a lot of other traffic happening with this NAS during all times of the day/night.

I did try the retry interval and the on failure action option. With the system trying more before failing i did get one to complete last night (probably on it's 2nd or 3rd try). However since these are also transactional backup I need them to complete during the day.

Hey EugeneZ,

I am using native SQL 2000. There is currently no 3rd party software being used. I've changed the schedule before with these backup's and they have helped but the issue continue's to pop up now and then again. Having to keep changing the schedule isn't a good enough option for a fix/solution for us.    

The NAS may need a 2nd NIC installed which is what I am considering and need to find out if that's even possible.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 38745290
Yes, if you could have a private network between the NAS and the SQL Server that may help. In other words, you may not be able to add a second NIC to team or load balance the primary connection, but you may be able to add one without a default gateway that has a private IP address and interconnects directly to a NIC similarly configured on the SQL Server. You could then add a static route and communicate with the NAS over the private IP, eliminating the noise of the production network.
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 250 total points
ID: 38745429
extra nic may or may not help (you need to check you router) also you need to add more memory on yiour backup file server; maybe faster HDD, maybe new server ..

 and try to use 3rd part sqlbackup with compression and network check during back\restore

http://www.idera.com/SQL-Server/SQL-safe-backup/
0
 
LVL 1

Author Comment

by:HckrAdm2005
ID: 38755273
Currently still working on the 2nd NIC. I ran some perfmon logs and found out it's not an I/O issue. Memory and CPU usage is well below thresholds. Our Nas is a Dell PowerVault nx3100.

When I ran one full backup from our sql2000 server to the NAS I also along with the perfmon watch the NIC and saw that it was at a constant area of 20-25% usage.

Then another scheduled backup Ran about 45mins later and failed (another sql 2000 backup). At this point it still looks like a NIC over utilization/saturation issue?

I did look at the idera software and tried a trail backup. The backup ran nice and compressed to a really small size. This is something i may look at down the road.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38757009
<20-25% usage.>  check the NIC speed settings  on the box. are these 2  teamed NICs? and btw are they 1Gbps or 100 Mbps? what is the router switch speed?
0
 
LVL 1

Author Comment

by:HckrAdm2005
ID: 38757236
There currently is only 1 NIC. I haven't teamed or done anything with a 2nd NIC yet.
The NIC ports are 1GB and the routers ports are 1GB as well.
0
 
LVL 1

Author Closing Comment

by:HckrAdm2005
ID: 38760546
Thank you guys for your help both of you helped me answer the problem. It is with NIC/network issues and needing a 2nd NIC for traffic. I have obtained proof and can progress forward internally.
0

Featured Post

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question