Post reply

Transaction log full

  • November 13, 2005 at 10:47 pm

    #169482

    I am a newbie. Matter most urgent. Can anyone tell me how to backup a transcation log. I am getting the error maessage log full, backup the transaction log. Any help would be appreciated very much.

     

    Thanks in advance

  • November 14, 2005 at 12:33 am

    #603590

    DBCC SHRINKDATABASE('database',10,TRUNCATEONLY)

    BACKUP LOG database WITH TRUNCATE_ONLY

  • November 14, 2005 at 2:11 am

    #603594

    This is very interesting solution , but works only if you have rights for Enterprise manager.

    Follow these steps:

    1) Right click on desired Database

    2) Go to All Task -- Backup

    3) Select Transaction log from the options

    4) Back up the database as desired location. 

    Once the backup has been done you will see that the transaction log has been backed up and the space is freed up.

     

    This is simple and trusted technique.

     

  • November 15, 2005 at 9:14 am

    #603964

    If you don't backup you transaction log on a periodic basis you probably should set your database to a recovery model of 'simple' rather than 'full' so that the transaction log does need backups

    Francis

  • November 15, 2005 at 9:36 am

    #603968

    It's not quite as simple as just changing the recovery model to SIMPLE to avoid doing backups. "FULL" recovery allows you to recover lost or corrupted data from a point in time between your full backups. "SIMPLE" does not give you that ability, you're limited to the last full backup. Consider the importance of each particular database to determine the best backup strategy for each DB. There are thousands of posts on this topic for more detailed info .... as well as Books Online (BOL).

  • November 15, 2005 at 9:51 am

    #603974

    To clarify.  I agree that you shouldn't change the recovery model to simple in order to avoid backups.  What I said was if you are not going to backup the transaction log then change the recovery model.  Of course the decision to not back up the transaction log should be based on the business need for recovery.  I suspect that in this case full backups may not be happening on a regular basis either.  But this was never said.  So perhaps full backups are being taken every day and there is just a problem with transaction logs filling up.  If this is the case then changing the recovery model is a good idea.  If this is not the case then the recovery model should not be changed and periodic transaction log backups should occur.  But BACKUP LOG database TRUNCATE_ONLY is not the command to use.  The transaction log should actually be saved somewhere for possible use.  See BOL for the actual syntax or use a database maintenance plan.  There are lots a great articles on this site about creating an appropriate strategy for backups. 

    Francis

  • November 30, 2005 at 8:18 am

    #606754

    Thanks for the response. We used the sp_dboption checkpoint - not sure if that was essential. Later we backed up the transaction log & executed DBCC shrinkdatabase also.

    Everything going fine now. Will try your solution next time we encounter a situation like this.

    Regards

    Jayanthi

     

     

     

     

  • November 30, 2005 at 3:09 pm

    #606905

    Sarang Bobde,

    This is very interesting solution , but works only if you have rights for Enterprise manager.

    your statement quoted above is misleading ,

     what is rights for Enterprise manager ?

     

     

    Kindest Regards,

    Sameer Raval [Sql Server DBA]
    Geico Insurance
    RavalSameer@hotmail.com

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

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