Monday, March 26, 2012

Maintenance Plan backup fails for large databases

Hi everyone,
I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
are all hosted on Windows 2000 Advanced Server. I have about 20
databases in all spread out among the 3 servers, ranging from 10MB to
100GB in size. I've had the same maintenance plans in place for at
least a year, and I'm having some trouble now.
In my maintenance plans, I do a nightly full backup of each database to
a network location. All 3 SQL servers back up to the same network
fileserver, via UNC. Until recently, everything was working fine, but
now it seems my 3 largest databases (all 10GB or larger) are showing a
failure in the maintenance plan history. The backups themselves are
completing fine, though, and I did a test restore from them to confirm
that. But the maintenance plan is returning the following errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft][ODBC
SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
abnormally.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
Backups\LW1SQL1\MyDatabase_db_200508090449.BAK' failed, status = 64.
See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
Obviously, because this is affecting all 3 of my SQL servers, it most
likely has something to do with the destination server. From what I
can tell, somewhere near the end of the backup of the large databases,
the destination server becomes unavailable to the SQL server. I
disabled all other services on that server (Antivirus, Tape Backup
services, etc) and did another test, but with the same results. I
installed updated NIC drivers and RAID controller drivers as well. I
can manually copy large files over the network to this location with no
problems.
Does anyone have any suggestions? I am baffled. Thank you.
--
george sarlas
gsarlas@.yahoo.comA few more details I forgot to post...
All SQL Server services are running under a Windows account with full
permissions to the UNC. Also, all databases in question are set to
FULL recovery mode. We are successfully doing transaction log backups
via log shipping plans. Thanks.
--
george sarlas
gsarlas@.yahoo.com|||C:\>net helpmsg 64
The specified network name is no longer available.
I have found it mostly to be one of 2 issues:
1. Network communication problems with switches/routers.
2. The destination server is not able to contact the domain controller, as
all of it's bandwidth is being used by the servers backing up to it, so it
aborts the connection.
#2 is solved by having a dedicated network over which backups are copied,
and not over the network interface that is used by SQL Clients and general
domain chatter.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<gsarlas@.yahoo.com> wrote in message
news:1123704481.634460.129430@.z14g2000cwz.googlegroups.com...
> Hi everyone,
> I have 3 different SQL Servers, all running SQL Server 2000 SP3a. They
> are all hosted on Windows 2000 Advanced Server. I have about 20
> databases in all spread out among the 3 servers, ranging from 10MB to
> 100GB in size. I've had the same maintenance plans in place for at
> least a year, and I'm having some trouble now.
> In my maintenance plans, I do a nightly full backup of each database to
> a network location. All 3 SQL servers back up to the same network
> fileserver, via UNC. Until recently, everything was working fine, but
> now it seems my 3 largest databases (all 10GB or larger) are showing a
> failure in the maintenance plan history. The backups themselves are
> completing fine, though, and I did a test restore from them to confirm
> that. But the maintenance plan is returning the following errors:
>
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3013: [Microsoft][ODBC
> SQL Server Driver][SQL Server]BACKUP DATABASE is terminating
> abnormally.
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Write on '\\backup-server\Data-Backup\SQL
> Backups\LW1SQL1\MyDatabase_db_200508090449.BAK' failed, status = 64.
> See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
>
> Obviously, because this is affecting all 3 of my SQL servers, it most
> likely has something to do with the destination server. From what I
> can tell, somewhere near the end of the backup of the large databases,
> the destination server becomes unavailable to the SQL server. I
> disabled all other services on that server (Antivirus, Tape Backup
> services, etc) and did another test, but with the same results. I
> installed updated NIC drivers and RAID controller drivers as well. I
> can manually copy large files over the network to this location with no
> problems.
> Does anyone have any suggestions? I am baffled. Thank you.
> --
> george sarlas
> gsarlas@.yahoo.com
>

No comments:

Post a Comment