?
Solved

SQL Server 2005 slow backup to UNC path

Posted on 2009-02-14
7
Medium Priority
?
1,091 Views
Last Modified: 2012-05-06
I occasionally watch SQL Server 2005 database backup network performance with the Networking tab in Task Manager, in an attempt to estimate if the backup will take a little time or a lot of time (minutes or hours, basically).  The backup runs from one server connected to the SAN to another server connected to the SAN (same SAN or different...not certain it makes a difference).  The destination is a server with either a hidden share in the path, or a visible share on the network (again, doesn't seem to make a difference).  All target paths are full UNC.  When running a SQL Server 2005 backup using standard SQL commands in a SQL Agent job, the backup will perform in any of three different scenarios:
- Start out with network utilization in double digits (10-30%, depending on other activities on the network), then drop off into less than 5% for the remainder of the backup.  This is the most common scenario.
- Wide swings of network utilization almost continuously.  I've seen this run with as high a variance of 60+%, with the low point being 1% or less.
- A steady utilization throughout the entire job, generally in the double-digit range.  This appears to be elusive...only seen it a few times.

Theoretically, what would cause the wide swings of network utilization?  What would cause the backup to start out in turbo mode and drop off badly afterward?  This is a significant issue because backups invariably fail with an error message "The specified network name is no longer available."  This occurs almost nightly.

Thanks!
0
Comment
Question by:ejohnson4211
  • 3
  • 3
7 Comments
 
LVL 63

Expert Comment

by:SysExpert
ID: 23643550
1) try with a mapped drive instead

2) What is CPU usage during the backup ?

3) Is there anyting else running that can cause network bottlenecks ( other backups ?? )


I hope this helps !
0
 

Author Comment

by:ejohnson4211
ID: 23643892
1) I'll suggest the mapped drive idea to the network admins, thanks!  What is the difference overall between the two approaches?
2) CPU is hardly a trickle usually...10% or less maybe.
3) The network is highly utilized in the company I'm currently working at.  But I've seen this behavior at other companies as well, so I can no longer ascribe it to other activity.

Here's the other thing that is unusual about this problem:  The profiles I mentioned earlier seem to have affinities to specific servers.  That is, of the two less-than-ideal scenarios listed previously, that same one will occur almost always for a given server.  I've run backups simultaneously among several servers, all hitting the same place, and watch them in the Network tab of Task Manager to find them operating differently on each server.  When I start up another backup on the same server where one just finished, it will have the same profile as its predecessor.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23646566
SysExpert,
   You cant use Mapped Drives for Backup and Restore in SQL Server as SQL Server cannot read mapped drives. In your backup and restore commands you always have to refer the network drive or network share using UNC path.

UNC path has the following format: \\MachineName\ShareName or \\MachineName\DriveLetter$\Path

<< Start out with network utilization in double digits (10-30%) , high a variance of 60+% >>

Yes it would be. Because as your data is moving to a network path and based on your network bandwidth and sharing this may vary.

Kindly run Perfmon on the server which has that network share and find out the File I/O, Read Counters, Write Counters and other disk storage statistics.

I suspect some problem in writing files in your network share more than the network bandwidth.

And one of my another suggestion is Back up the Database to a different SAN if possible.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23646584
<< The specified network name is no longer available >>

This can be resolved in any one of the approaches:

1. Open the firewall on the destination machine for this machine.
2. Check whether Computer Browser Service in your Network share server is running.
3. Or You may be using Symantec Endpoint Protection in your machine. If Yes, this is a known issue as per Symantec and a fix is available:
http://support.microsoft.com/kb/961293
0
 

Author Comment

by:ejohnson4211
ID: 23662026
Thank you rrjegan17 for your comments.  I will follow up with Networks Services when I'm onsite later this week and post results or additional findings.
0
 

Accepted Solution

by:
ejohnson4211 earned 0 total points
ID: 23751321
It appears the issue was with the cache card on the SAN.  The cards were installed and charged (not certain yet what that means) and I'm running a test backup now.  Network utilization is running about 9-15%...better than before, but may not still be up to par.  When the backup finishes, I'm going to compare performance with a backup I took yesterday that was pointed to the previous target server.

Likely I will close this out tomorrow.  Thanks!
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1600 total points
ID: 23752951
Ok.. Then I have one more suggestion out for you:
To confirm whether this is a problem with your cache or in the disk, run perfmon test on both the server machine and the destination server where your backup is stored. check this one out

http://adminfoo.net/2007/04/windows-perfmon-top-ten-counters.html
http://knol.google.com/k/brent-ozar/sql-server-performance-monitoring-with/1f7zsjm9jopw/2#
http://blogs.technet.com/vipulshah/archive/2006/11/30/understanding-perfmon-counters-while-troubleshooting-sql-server-performance-issues.aspx

Hope above perfmon tests will help you to narrow down to your exact issue.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month16 days, 19 hours left to enroll

862 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