LDF AND TRN FILE KEEP GROWING

  • Can someone help me with this ?

    As is -

    Version - SQL Server 2005 Standard SP2

    SQL DB Recovery Model = FULL

    I run Transaction Log backup at 1PM and 1AM daily, using below script

    BACKUP LOG @DatabaseName TO DISK = @Path

    I run Database backup at 1:15AM daily, using below script

    BACKUP DATABASE @DatabaseName TO DISK = @bakfile WITH NOFORMAT, INIT, SKIP, REWIND, NOUNLOAD

    Observation -

    Despite the transaction log backup every 12 hours, my LDF file keep growing. The TRN backup file size is also growing, to almost the size of the backup BAK file.

    This is odd because there isnt a lot of transactions to the database within a 12hours windows. The DB backup file size is about right, but I was expecting the LDF to be kept under control, and TRN files size to be much smaller.

    What has not been configured correct ? Can anyone check if my Transaction Log backup script is correct ? Do I have to add more parameters, e.g WITH INIT ?

  • Do log backups more often.

    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
  • thanks, but i still think the amount of transactions in the database should not warrant the current size of the LDF or TRN file.

    How about my transaction log backup script - "BACKUP LOG @DatabaseName TO DISK = @Path" , is this command sufficient ?

  • http://www.sqlservercentral.com/articles/Administration/64582/

    http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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