Post reply

Unabled to take up backup. Transaction is Full

  • December 13, 2005 at 8:11 am

    #111112

    Hi All,

    Please help on this problem.

    I have database of nearly 9 GB and i am not able to any transaction in the database as well as through application also. While taking the backup, the sql server 2000 enterprise manager shows the following as a error:

    " The log file for database <databasename> is full. Backup the transaction log for the database to free up some log space.

    Backup Database is terminating abnormally.

    Could not write a checkpoint record in database id (No.) because the log is out of space."

    The same error is occuring while doing the operation like, shrinking the database, shrinking the datafile, shrinking the transaction file.

    I will very helpfull, if any one can solve my problem.

    Thanks and Regards,

    Murali S

  • December 13, 2005 at 9:04 am

    #609241

    How about adding some log space and try your backup process again.

     

  • December 13, 2005 at 9:41 am

    #609258

    If you don't have to keep the transactions that you currently have in the log, you can run the following:

    backup log

    with truncate_only



    Michelle

  • December 13, 2005 at 9:44 am

    #609260

     

    As your Db is 9 Gb and also it looks like you are having enough transactions that generate sizable log.

     

    What I suggest you is to have Full Database  Backup  through a Job that executes

    once in day. If your system stays busy in day then schedule job for midnight.

     

    This job needs 2 Step

     

    Step 1: Truncate Log and Bring Log file to Normal Size

     

    Backup Log  DbName With Truncate_Only

     

    Dbcc Shrinkfile(DbName_log, 100)

     

    Step 2:     Full Database Backup

     

    Backup Database DbName

    To Disk = ‘C:\foldername\DbName.Bak’ 

    With Init, Name = 'DbNameBackup'

     

    Once you have created this job and scheduled properly. You can avoid this problem in future.

     

    For immediate solution execute this job once after its creation.

     

    Hope this helps.

    Kindest Regards,

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

  • December 13, 2005 at 11:34 am

    #609278

    Sameer has great advice.

  • December 13, 2005 at 11:59 am

    #609285

    Thank You Steve for appreciation.

    This is wonderful site and I highly acknowledge your Sql server skills and writing.

    I would like to have your insight in one of my question here in this forum [12/5/05] related to maintenance plan issues in 2005. There are 81 viewer

    but not a single answer. Please look into the problem.

    It will be great to have some response from scholar like you.

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=241231

    Thanks,

    sameer_raval@hotmail.com

     

    Kindest Regards,

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

  • December 14, 2005 at 1:58 am

    #609404

    Thanks for all.
     
    Finally i have taken a backup with the following procedure:
    1. Backup log transaction with truncate only option and
    2. Shrink the transaction file.
     
    the same has sameer raval suggested.
     
    Regadrs,
    Murali S

  • December 14, 2005 at 5:00 am

    #609423

    Hi Murali,

    In addition to the above, I would add that if you do not need to back up the transaction log, you should set the Database Recovery mode to 'Simple' (find this in database properties).  This will mean that transactions will be removed from the log once they have been committed to disk \ memory - so the transaction log should always stay small.  However, if the database is critical, and losing data would be a problem, you need to look into backing up the transaction log instead.

    Cheers,

    Martin

     

  • December 14, 2005 at 8:48 am

    #609491

    Murali,

    You should probably look at your backup and recovery needs in order to come up with a good plan.

    In my environment, I have a couple of servers.  "SQLOPS" and "SQLDIST01"

    The first is the main database server.  I have about 200 anonymous mobile subscribers connecting to a database called 'USFS'.  Our web application also connects about 30 operations folks to the database as well.

    I also have a bunch of Crystal reports supported by stored procs in a database called 'CustomReports'.

    I need to be able to restore to a point in time in the USFS database, so I use the FULL recovery model and back up the transaction log every 15 minutes.

    The report sprocs don't change very often, and I've got Mike (developer) to FINALLY use source control.  So I use the simple recovery model for the CustomReports database and just do full backups.

    On the other server, I have my EDI feeds running at different times during the day.  For that 'DataExchange' DB, I use FULL recovery and back up the transaction log at specific strategic times during the day.

    hth

    jg

     

     

     

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

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