November 21, 2005 at 3:21 am
Hi,
I'm running out of ideas here, so I thought I'd ask the profs.
I've got a 26 gig database which I'm trying to backup and zip using a scheduled DTS. I can't backup the database to a local disc since none of the discs in the machine has enought capacity left to backup the database to. (55 gig drive, 26 gig db, some SQL server files, a growing Logfile and all i've got left is 22 gig, which is not enough).
So I'm trying to write the logfile to a networkshare, a NAS or another DB server that has enought capacity. But then I stumble upon a timeout. A 16 gig Database works just fine, backups, zips and get downloaded to a storage center, but the 26 gig generates a timeout and stops. Not a good way to garantee a working backup.
So my question is, can I set the timeout period of SQL Server 2000? Or are there any other options other than increasing the disk size (which would result in a downtime)? I figure I'm not the only one with this problem and like to hear any ideas you guys have.
FYI: I'm a ASP-developer who got pushed in the role of semi-DBA/network admin since the former DBA/Network admin quit.
I'm running a Win2003 standard with SQL 2000.
(The backup DTS backups using VBScript (executes a BACKUP DATABASE query) and when an errors occurs (handled using On Error Resume Next) it gets mailed to me. The error is "Timeout expired".)
November 22, 2005 at 6:39 am
Hi,
you might want to use a backup software that uses on-the-fly-compression, like LiteSpeed from Imceda (Quest) or SQL Backup from Red Gate or ...
We are using LiteSpeed here, mainly because it includes stored procedures to do the backups. The resulting backup files sizes are approximatly 1/5 of an native backup.
regards
karl
Best regards
karl
November 22, 2005 at 9:52 am
Out of your options a NAS device is the best. However you are still exposed to the perils of a network. Issues such as bandwidth, latency and load will affect you. I'd also turn off 'VERIFY' as well. But in any normal 100 BaseT network you should be able to backup a 26 Gb database without issue. At present we are changing all of our database backups from SAN disk to NAS disk (reasons are cost and site to site replication). Some of my initial testing/benchmarking indicates that NAS and SAN backup files perform about the same. I've successfully backed up databases up to 140 Gb so far without incident (most of my databases range from 10-40 Gb in size).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 22, 2005 at 11:27 am
"generates a timeout and stops"
Is the hardware HP NICs and Cisco Routers ?
There seems to be a problem with this hardware combination communicating the appropiate speed and duplex when under high load, such as when you backup a large database.
Check the NIC card configuration on both the DB Server and the File server for "Speed & Duplex". If set to "auto" change to the appropriate fixed value such as "100 Mb Full" or "1000 Mb Full". This change will probably require a server reboot.
SQL = Scarcely Qualifies as a Language
November 22, 2005 at 3:09 pm
We have just gone through and performed the same thing on all of our servers - changed the NIC cards to 100 Mb Full Duplex. The remaining piece is to set the switch ports that the servers to 100 Mb Full Duplex as well.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 22, 2005 at 6:57 pm
Just for future reference is resolving network timeouts, keep us posted on whether just the NIC need to be set or if the switchs and routers also need to be set.
Cheers
SQL = Scarcely Qualifies as a Language
November 28, 2005 at 10:28 am
Carl, in our case we needed to set both the NIC and the switch port (now it is the site standard).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 28, 2005 at 1:19 pm
This depends on the switch, but in most cases both need to be set as rudy suggests. Some switch manufacturers like ADTRAN allow for 100 MBs Full, but request users set their switch ports to Auto.
December 1, 2005 at 9:34 am
After checking all those good network suggestions above try doing it without DTS and VBScript, (backup query, xp_cmdshell for zipping) it had worked fine for me for years (if you don't have to be too security tight!)
Hope this helps!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply