Post reply

performanceissues while saving transactionlog

  • February 7, 2007 at 2:10 am

    #68271

    Hello,

    im brand new to sql server.

    my problem is .. we backup transaction log every hour.. and every hour at the same time the cpu usage and the avg disk read queue length from the harddisk where the database is placed increase alot

    the backuped trn file is on a separate disk and only 50-100 mb every hour.. why needs the

    job 10 mins to finished..

    thx for help

     

    Rene

  • February 7, 2007 at 3:47 am

    #688228

    The most likely cause is poor hardware, certainly disk queues mean poor disk subsystem. This really isn't a quetion that can be resolved just like that.

    There's lots of info on this site, BOL and various books. If you're new to sql server then get on a training course as that will kick start your learning no end.

    If you're new to sql server how do you know you have a problem? To be honest what you're posting is the same as saying when you drive your car it uses petrol, what should you do?

    A t log backup reads data from the drive where the ldf resides to where ever you are placing your .bak/.trn file. These should be seperate drives, not partitions on the same drive. T Logs ( the ldf files ) should be on a raid 1 to improve write performance. Your backups would be best on a raid 10, raid 5 is rubbish for writes. If it's taking 10 mins for a small backup then you need a faster disk subsystem or segregated drives or both.

     

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 7, 2007 at 4:12 am

    #688238

    HI,

    The ldf file is on a separate harddisk on a separate array controller with raid 1 the backuped trn file also.

    i wonder why i have no performace issues there.

    Only the drive with the database / 10 drives ( raid 10 ) has a increase of average queue read lenght while i backup the transaction log which is placed on an other drive / array / arraycontroller.

    rene

  • February 7, 2007 at 5:03 am

    #688252

    depends what you mean by increase - if the queue goes above 5 then I'd be worried. I don't have any similar setup that i could test to see if I get the same result. I never use disk queue counters ( mainly as on sans they don't work ) I always use the io completion time, this is a much better indication - have a look at that counter for all the drives when your log backups run.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 7, 2007 at 5:33 am

    #688257

    hi,

    i mean an increase from 0,6 to 4,0 but only when i backup transaction log.

    the performace from the other harddisks is ok  ( log , trn ) , no changes there while i backup the log.

    maybe its normal . dont know

    btw thanx for help

    greets rene

  • February 7, 2007 at 5:45 am

    #688260

    certainly backups are resource intensive, as you'd wish them to be - I don't have the test lab setup to run a series of tests like this currently but when I've built my test lab I'll look into this to see if I can reproduce it.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • February 12, 2007 at 4:28 am

    #689115

    HI,

    i guess the maintaincance plan was corrupt .. still dont know why

    i deleted the old maintanance plan and created a new one..

    all problems are gone now...

    Greets Rene

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

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