SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactional and Database Backups failing


Transactional and Database Backups failing

Author
Message
forceman29
forceman29
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 162
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 Smile
Joie Andrew
Joie Andrew
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2313 Visits: 2032
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.


You stated that you moved the backups from the SAN to the NAS. Were backups working after that move and just recently fail, or have they been failing since the move? Have you tried accessing the network location from the account the SQL Server service is using? It will need permissions to the network share.

Joie Andrew
"Since 1982"
forceman29
forceman29
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 162
Hey Joie,


The backups did work when I did move them from the SAN to the NAS for several months. But due to increasing traffic on the NAS (which from what I have seen that is happening) the transactional backups during the day are starting to fail.

The account that the maintenance plan is using does have the correct permissions for the NAS.
forceman29
forceman29
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 162
After more research and getting other people's help I was able to determine it's a NIC congestion problem.

What i need to do is just add a 2nd dedicated NIC for this old server to get the backup's off of production and make sure our backup scheduling is ok where they are not conflicting to much with other servers.

Thank you all for your help, ever little bit helped me get to my solution Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search