Friday, March 16, 2012

SQL Transaction Log is full Error.

Issue: Transaction log is full error when users are running any transaction on sql server.

When does this error comes up? When user is trying to run transaction and there is not space on transaction log file you get 'Log Is Full' error 9002.

How to Resolve this:
  1. Shrink Transaction log file because that's the quickest way to reduce size of log file.
  2. Take log backup.
  3. Make sure Log file's initial size is not too high. Sometimes Initial size of log file increased so much that we cant even shrink it. Try to change initial size of log file by using following command:
         ALTER DATABASE DB_NAME MODIFY FILE
                                       
(NAME = 'Logical_log_file_name', SIZE = 200)  

    4. Then shrink log file again.

    5.  If you dont see any changes in log file size then last thing you do is to change your database's recovery model to simple and then bring it back to full.
    6. This will solve your Log Is Full error message.

If above process doesnt work, then try following. ( If recovery model is full )
  1. Change database recovery model to simple.
  2. Then again change it back to full.
  3. Shrink database and you should be fine.

No comments:

Post a Comment