SQL 2005 Transaction log full

---

If you get the error message:

Error: 9002, Severity: 17, State: 2

The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

These are some of the reasons that caused this problem:

• A very large transaction log file.
• Transactions may fail and may start to roll back.
• Transactions may take a long time to complete.
• Performance issues may occur.
• Blocking may occur.

But bottom line is that is time to do something with the Transaction Log. Something you can do is start a clean transaction log (Be sure you have a backup to be save).

1. Select your database, right click on it, go to Tasks >> Take Offline, we need this step in order to be sure nobody is using the data base.
2. Now highlight the database, go to Tasks>>Detach.>> Click OK
3. Go to the folder where the log file is located, then rename  the *_log.ldf to be like *_log-jj.ldf
4. Now it is time to retach the database, go to Databases and right click >> Attach…>> Click Add >> add the database you detached
5. Select the log file and click the ‘Remove’ button so you only attach *.mdf file
6. At this moment a new transaction log file is created and you can delete the old one.

---