Transaction Logs BackUp

Question

Transaction Logs BackUp

Jeetendra

SSCrazy

Points: 2076
More actions
July 10, 2007 at 3:53 am

#372763

Hi Gurus,
I have a Problem with the Transaction Log BackUp's.
It Runs every 15 Minutes and since its a Production Server we have very High Transaction Load.
There are around 20 Databases on server and every 15 Minutes there is a Peak showing in SAN Drives.
I want to minimise this Peak.
Is there any other Solution to this.
Thanks for your Help.....!!!!!!!!!!!!!!!!!

Kindest Regards,
Jeetendra

Viewing 11 posts - 1 through 10 (of 10 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

Do you have the backup going to different drives from the log and/or data? That should help. Also, do you need all databases backed up every 15 minutes? You might be able to set different schedules based on different requirements.

Jeetendra SSCrazy Points: 2076 More actions · Answer 2

Hi Steve,

The Full & Tans Log BackUp(Both together) go to a different SAN Drive apart from the SQL DATA & Log Drive(Which has a separate SAN Drive for each).

Also the Policy of the company wants to backUp Trans Log Every 15 Mins.

Thanks

Kindest Regards,

Jeetendra

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

The only way to minimize the peaks are to run log backups more often (so less data in each backup) or use hardware to increase the speed. Using a R1 or R10 drive setup might help.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

Go for better hardware. Also use RAID levels that does not impact much on write performance.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Jeetendra SSCrazy Points: 2076 More actions · Answer 5

I am Introducing a Delay of 30 Seconds for Trans Log BackUp between each databases.

Will this be a correct Solution.

Kindest Regards,

Jeetendra

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 6

Are you sure that the other databases tlog backup will complete within 30 secs else you will have the problem accompanying right.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Jeetendra SSCrazy Points: 2076 More actions · Answer 7

One more Interesting Fact to share is that the SAN Drive is shared by the SQL Servers of other applications as well.

1 Most Important thing is all the SQL Servers run Trans Log BackUp after every 15 minutes.

A little bit more the SAN Drive configured for the FULL & Trans Log Backup shows a Sharp rise in READS and not Writes.

I am confused now how SQL Server performs READS operation on SAN Drive configured for the FULL & Trans Log Backups and why for what reason?

Thanks.

Kindest Regards,

Jeetendra

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 8

Does the database being used for Transactional purpose and is there large number of updates/deletes/insert happening every second. If so then there is a possibility as SQL server reads backup file and the datafiles so that the backup is consistent with the state of database till the time the backup is run.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

george sibbald SSC Guru Points: 104210 More actions · Answer 9

y0u will see an i/o spike on the drive containg the logs and the drive you are backing up to when the backups take place, this is unavoidable.

your idea of spreading out the backups so they do not all occur at the same time is a good idea and definitely worth doing.

ensure if you can log and backup drives are seperate drives through different channels

Other than that best you can do is get the fastest disks you can and use raid 10.

Are the io spikes actually causing a slow down to the apps? Is average disk q length unnacceptable?

---------------------------------------------------------------------

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 10

RAID 1+0 is the best other option that i forgot to say anyways thanks for george for having rminded for that.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com