January 18, 2011 at 1:41 pm
I have a backup job that uses litespeed to backup databases to a network drive, recently i found that backup file after 20GB it stops backing up and the job never ends, i have to restart the sgl agent to stop the job and restart the job again but still the problem persists, iwill stop backing up after some time.
I tried runing the same backup job on local drive and to some other networks drives which works fine except this particular network drive and someitme i dont even see the backup file created but the job seems to be running.
January 18, 2011 at 3:55 pm
There are a lot of reasons something like this can happen. It can be cause by the SMB settings on the destination server/host, or various settings on the host.
To validate this, you should try manually copying a file to the destination and see if it fails or hangs the same way.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 19, 2011 at 12:13 am
I have found backups to network shares to be somewhat problematic. I haven't had any real issues with restores from network locations, except they can take longer under heavy network loads.
I have found that doing the backups to a local directory and then moving to a network location works better.
January 19, 2011 at 7:05 am
Ditto to Lynn's comment.
I've found that if there's network connectivity disruption during a file move, that the connection on my side doesn't necessarily know that it's been broken. So the process will spend hours spinning its wheels, thinking it's doing something it's not actually doing. Therefore, it's much easier to do the backup locally and just move it. Saves me a lot of grief in the long run because I still have a backup, even if I can't get to the network share.
January 19, 2011 at 1:59 pm
I like the idea of copying to network drive after backing to the local drive for which i have to convince my team for that.
anyways the problem is changed now.I cant even see the backup file when i start the job and the job/backup query never ends though i kill the process, it still says "cancelling query" since 2 days. Is there a way to stop that rollback/cancelling the query without restartgin the service?
Here is the problem to better understand...
SQL Server machine A to Backup Server B - problem as mentined above
Other SQL Servers to Backup SErver B - No problem
SQL Server A to Other Machines - No problem.
So i think the problem is only with the link between SQL Server A and Backup Server B, Please let me know what i can check for or ask my network admin to check for.
January 20, 2011 at 5:36 am
Tara-1044200 (1/19/2011)
anyways the problem is changed now.I cant even see the backup file when i start the job and the job/backup query never ends though i kill the process, it still says "cancelling query" since 2 days. Is there a way to stop that rollback/cancelling the query without restartgin the service?
Not really. SQL will continue to rollback until it's done. That's part of the ACID process. Usually it's a good thing. You can try killing the SPID with the KILL command, but that might not do anything if the server thinks it's still rolling back. And KILL rolls back commands when it's used.
If you are truly concerned something's wrong, and it's blocking other work or causing other problems, you can restart the services. But there are a dozen DBAs who just read that response of mine and want to lynch me for it because a service restart is the worst response (and the last resort) to any SQL Server problem.
Tara-1044200 (1/19/2011)
Here is the problem to better understand...SQL Server machine A to Backup Server B - problem as mentined above
Other SQL Servers to Backup SErver B - No problem
SQL Server A to Other Machines - No problem.
So i think the problem is only with the link between SQL Server A and Backup Server B, Please let me know what i can check for or ask my network admin to check for.
Do you know how to do TraceRt or Ping? TraceRt is better; it gives more information. Do one between Server A & Server B. If that doesn't work, have the network admin set up a sniffer on the servers for a certain time frame, then run the backup process again so they can see everything that's going on.
Give the network team all details and output from any TraceRt or Ping that you run. Also, check PerfMon on the servers, check the network card setup to make sure the devices are functioning correctly, and ask them to make sure there are no loose wires between the servers. Sometimes, that's all it is. Someone accidentally half-pulls out a network cable and suddenly communications between servers are intermittent and sporadic.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply