Post reply

Transactionlog does not shrink after backup

  • July 9, 2009 at 5:22 am

    #136092

    I have a SQL 2000 server that creates a transactionlog backup every 15 minutes. I have to do this because the logfile will grow really fast if I don't back it up.

    The weird thing is that the logfile should shrink when the backup is complete but in some way it doesn't.

    execute master.dbo.xp_slssqlmaint N'-PlanID 94615F0C-6402-4D6E-AC2E-94CADCBFFE83 -BkUpMedia DISK -DelBkUps 4DAYS -BkUpLog "E:\DBDUMPS" -CrBkSubDir -BkExt "trn" -NOOPTOLR -Throttle 100 -BufferCount 20 -MaxTransferSize 1048576 -CompressionLevel 1 -Priority 0 -Logging 0 -Affinity 0 -WriteHistory '

    I use Litespeed to create the backups because they would be too big otherwise. Litespeed shouldn't be the problem because I use it with several other SQL 2000 servers with the same SQL Server version (SQL Server 2000 SP3).

    Can anybody give me a lead what possible could be the problem.

    Thanx in advance

  • July 9, 2009 at 5:42 am

    #1022277

    use dbcc shrinkfile

    or backup log with truncate only option or set recovery model to simple

  • July 9, 2009 at 5:49 am

    #1022282

    Its a common misconception that it's a good idea to shrink the transaction log. If you do, it will just grow again.

    If you have backups every 15 minutes, that's fine. The transaction lof itself will settle at a size and probably stay there. Backing it up just leaves more space for the next transaction to be logged.

    The only time it's worth shrinking it is if you took some unusual activity that made it grow much bigger than it would in everyday use.

    Tim

    .

  • July 9, 2009 at 8:50 am

    #1022437

    ramuvanparti (7/9/2009)

    or backup log with truncate only option or set recovery model to simple

    That's very bad advice to give someone since you don't explain what the consequences are of doing either of those.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2009 at 9:33 am

    #1022492

    Go with Tim's suggestion. Just run the log backups, don't shrink it.

    And don't mess with the recovery model unless you know what you are doing. Certainly don't truncate it if you need this data and want to be able to recover to a point in time.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 10, 2009 at 12:26 am

    #1022856

    I know the consequenses about setting the recovery mode etc and I don't want to set it to simple because I want to be able to restore to a point in time.

    The weird thing is that the logfile grows from 100MB to 45GB overnight.

    I'll create a job that will shrink the file but it's weird anyway because I have this on no other server. I'll have to work around it fortunately the server is planned to be upgraded to SQL 2005 maybe the problem is solved after the upgrade.

    Thanx for the advise

  • July 10, 2009 at 12:36 am

    #1022859

    sanderstad (7/10/2009)

    The weird thing is that the logfile grows from 100MB to 45GB overnight.

    Some activity is happening at night that's consuming heavy log file space? I suspect it's index rebuilding? can u check out what's happening during night that's causing massive log growth?


    Pradeep Singh

  • July 10, 2009 at 12:45 am

    #1022864

    sanderstad (7/10/2009)

    The weird thing is that the logfile grows from 100MB to 45GB overnight.

    There's some operation overnight that requires 45 GB or so of log space.

    Rebuilding all indexes?

    Doing large data archiving?

    Loading lots of data?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 10, 2009 at 2:15 pm

    #1023333

    sanderstad (7/10/2009)

    I know the consequenses about setting the recovery mode etc and I don't want to set it to simple because I want to be able to restore to a point in time.

    The weird thing is that the logfile grows from 100MB to 45GB overnight.

    I'll create a job that will shrink the file but it's weird anyway because I have this on no other server. I'll have to work around it fortunately the server is planned to be upgraded to SQL 2005 maybe the problem is solved after the upgrade.

    Thanx for the advise

    Shrinking the file everynight - just so it can grow again to 45GB is a waste of time and is causing you to suffer through performance issues. You shrink the file down to 100MB and probably have a very small increment for growing the file (default is either 1MB or 10% growth). With that you end up with a lot of autogrow events - every night.

    If that system needs is growing the log to 45GB every night, here is what I would do:

    1) shrink the file one time to 100MB

    2) right after shrinking the file - manually grow the file to 50GB in one pass (if possible). If this takes too long, grow it to 50GB at 5GB intervals.

    3) Leave it alone after that

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 9 posts - 1 through 8 (of 8 total)

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