Time out in VB.net when SQL transaciton log backup schedule changed to hourly

0 pts.
Tags:
SQL transaction logs
VB.NET
VB.NET 2005
We have changed our back up schedule on transaction log from daily to every hour recently. Now my VB.net 2005 program start getting fail to read the table error (maybe caused by Timeout) during the time backup is executing. Is there anyway we can prevent this? Thank you, Paul Hsu
ASKED: March 30, 2006  12:51 PM
UPDATED: August 28, 2008  2:18 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Rather than doing a direct backup of the transaction log while the database is in use, you should consider using log shipping to replicate the transaction log to a standby server.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DaveInAZ
    I'd say your guess about a timeout is probably correct. Depending on what database and connection method you're using, you should be able to increase a setting/argument called something like "ConnectionTimeout" either in your application or on the server (or both!). But the best way to prevent this would be to upgrade either the database server or its administrator. Unless your system is prehistoric or extremely overstressed and underpowered, and therefore totally unreliable, it's totally unnecessary to back up the log that often.
    0 pointsBadges:
    report
  • BeerMaker
    The only way to prevent this it to change the connection timeout in serveral places. The timeout is triggered on the client side when a response has not been returned prior to the timout expiration. Set the timout on (a) the database connection object or (b) the recordset (overriding the connecton timeout. Getting a timeout on a read sounds curious. Have you checked to see if you have some other problem. For example, what kind of record locks are you requesting (optimistic?). To speed up your reads verify you are using client side cursors and make sure your doing an indexed read. Good luck
    0 pointsBadges:
    report
  • Mortree
    Log shipping is better. Or if you wrote your desktop application to use procedures on the SQL server you have those central procedure manually write database files out for backup (watch for race conditions and expect desktops to pause for file writes). But the SQL Agent is what most people use with say 3-10 disk files for transaction logs to hit in rotation. But as an explanation you might want to check how that new backup is defined. Errors sometimes happen when making silly litle changes. My biggest suspicion accidental SINGLE USER Mode, if you didn't error in programming a ridiculously short wait for timeouts. SINGLE USER Mode would bounce you immediately without even waiting. I assume that backup is backing up to another server harddrive (fast) rather than tape (minutes) or over a slow dialup network connection. It maybe doing a full back up which means dropping into SINGLE USER Mode or flushing transaction logs to the database which can be similar in impact. The latter has to do with there being two ways to back up transaction logs. Read carefully. I seem to remember there is also a way to programmatically tell SQL to start another transaction log file while you select an old one to back up. But programming SQL wasn't my forte.
    0 pointsBadges:
    report
  • Rlreid
    If your trans dumps are killing your apps, they are taking too long; and need to be done more frequently or to use faster hardware. What, precisely, is the error returned by the database library you are using (and what library are you using? OLEDB, MS dblib, FreeTDS, etc?) What's the hardware for the translog, and the dump file, and are they sharing it with anything else? What's your checkpoint interval? Assuming the hardware is appropriate, it sounds like you should be dumping logs a LOT more often. By dumping more often you keep the log short, therefore it dumps faster. The transaction logs should be on thier own, very fast device (i.e. NOT on a SATA, USB, etc!). The dumps should be to disk, not tape (you can then copy the dumpfile to tape). SATA is OK for dumps, USB is not - too slow. SATA is NOT OK for trans logs - unless it's part of a high end iSCSI array. Most of our dbs dump logs every 10 minutes and I have never had an issue with the trans dump blocking other work. The only time I've had issues was when I didn't realize the tempdb translog was on a vanilla SATA disk array. Increasing the timeout in the app is doable, but is frankly a hack, not a solution.
    0 pointsBadges:
    report
  • PaulCHsu
    Thanks all for your help. We found out that the backup schedule change not only causing time out on my program but the whole system have been suffer from this change. Our consultants have been working on this problem now. Thanks all again. Paul
    0 pointsBadges:
    report
  • PaulCHsu
    The problem has been solved: When the Consultant changed the backup schedule, they not only backup the transaction log but also do the Database Integrity checks. This is the system killer. After they took out the Database Integrity checks, everything go back to normal. Paul
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following