Transaction Log backups causing problems

Question

Transaction Log backups causing problems

Anders Pedersen

SSChampion

Points: 11410
More actions
June 11, 2007 at 6:31 am

#120354

Last week I set up transaction log backups finally on my main database here (I took over the operations here about 8 months ago, finally getting on top of things). I backup to a 2nd server, with a gigabit switch between them (one hop, on the same switch).
Now the application that uses this database (web based application, internal use only) suddenly have a problem. It once in a while get a 505 error (page can not be displayed) on saves. I have turned tloag backups off for today to see if we still have the problem or not. There are no errors, and I do not see where the stored procedure that does the save start but does not finnish (have a trace running).
Anyone else ever seen a tlog backup causing problems? In 12 years of being a DBA I have never had problems with this.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 1

Hi,

if you back up to a different server a whole lot of factors get added. There can be DNS problems, permission problems, physical problems, driver problems...

What's the size of the log files, and are the server network cards gigabit too? Are backups and user requests going over the same network card? Might be a bandwidth problem?

I'd prefer to do the log backups locally, then copy the files to the second server.

Did you try to back up locally yet?

regards

karl

Best regards
karl

Anders Pedersen SSChampion Points: 11410 More actions · Answer 2

Gigabit on each server, each log backup (except the first one of the day after batch) had a max size of 35 MB and took around 4 seconds to run.

It does share the same network card with user request.

Would this be any different than copying the files though? My full backups go to local disk, then immediately to tape. In the past I have copied the 7GB full backup to the same second server in the middle of the day without causing a problem.

My next step is to try to backup to local server.

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 3

Copying and doing direct backups would generate the same amount of network traffic. I prefer local backups simply because i can do the backups even if my second server fails. In an environment with a heavy load i need to do the log backups to ensure continuing service - the log might run full.

So back to your problem - is the tape locally attached, or via network? if via network then there should be no problems with the bandwidth.

Is the size of the transaction log file big enough, or might it get full during the log backup?

karl

Best regards
karl

Anders Pedersen SSChampion Points: 11410 More actions · Answer 4

The tape backup only happens at aronud 3:30 in the morning after the full backups are done running to local disks. The transaction logs then run to the second computer in case of failure of the main computer, but they are not backed up to tape.

There is 18 GB of disk space free on the drive I have the log files on (on a SAN).

What's weird is that there are no errors on the server that SQL is on. The only indication is that IIS gives a "failure to display page" to the application.

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 5

What level of cpu activity do you get during log backups?

Best regards
karl

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

CPU's on that server never registers above 30% unless I intentionally try to stress them

I did a full backup (locally) of the 65 GB database in the middle of the day one day, and they sustained about 40% CPU the entire time, with no complaints about performance whatsoever.

I changed the backup to run the TLog backups locally, see if the problem resurfaces with that. If it does not will then simply copy those files after the SQL backup is done to remote server.