Post reply

Error 9002 transaction log for database "abc" is full

  • February 9, 2010 at 1:50 am

    #138822

    Hello Everyone,

    I am getting one error

    Error 9002

    transaction log for database "abc" is full.To findout why space in the log can't be reused see log_reuse_wait_desc column in the sys.databases.

    Version : SQL Server 2005

    Database compatibility : SQL Server 2000(80)

    Recovery model : FULL

    Logfile intial soze : 45090 : Restricted growth 46964

    datafile intial size : 2536 : unrestricted growth 1mb

    I taken the full database backup it's completed successfully.

    Please guide me,How to solve this issue.

    Thanks,

  • February 9, 2010 at 2:03 am

    #1115978

    Hi Ram,

    As suggested in error check log_reuse_wait_desc column in the sys.databases for 'abc' database.

    Your logfile growth is restricted to 46964 MB. There could be transaction running which requires size more than this to complete. If possible remove this restriction (if the drive has free space).

    Is transaction log being backuped up regurarily? If not do that.

    If database 'abc' is not being log-shipped or replicated or mirrored..try truncating your transaction log or If transaction log is never backuped up do so then transaction log will be truncated.

    Above actions will help to free some space in transaction log.

  • February 9, 2010 at 2:18 am

    #1115983

    if there is no other option than Truncate, then change the model from "Full" to "Simple" so that the database could contain the active portion of the log, then take backup of the database [full backup] and truncate the log file.

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • February 9, 2010 at 2:28 am

    #1115988

    mind well..its dangerous to shift from Full to Simple if its PROD database...Read explanation on database recovery models on BOL.

  • February 9, 2010 at 2:58 am

    #1116001

    Please read through this - Managing Transaction Logs[/url]

    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

  • February 9, 2010 at 3:26 am

    #1116010

    Thanks for everyone,

    I was taken full database backup

    and after i taken transactional backup

    that bkp size arround 13,500KB

    Now again one more time i taken transactional backup

    this time bkp size is 600kb

    And also now it's showing avalibale free space in the shrink dialog box (tasks >> shrink >> file >> logfile)

    I think it's fine now.

    Is this correct way...

    If we take the transactional bkp then logfile automatically truncated.

    Now it's fine i think ......@

    This is only solution for this ?

    so can we continue like this... ????

    Is this correct way ???

    or any other permanent solution is there ?

    Thanks,

  • February 9, 2010 at 4:02 am

    #1116021

    Good to know that it helped you.

    Suggestions:

    1. Read the link suggested by Gila

    2. Always remember that you should backup your transaction log regurarily which helps to manage your TLog space as well as in case of disaster it will help you to minimise the impact of dataloss and you can recover the data to point-in-time if you have proper backup strategies in place.

    3. It helps for performance gain too.

    4. Place your data and log files on different drives.

    5. Keep on reading BOL and this community forums in free time, you will learn a lot.

    Best of luck 🙂

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

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