URGENT !! How to clean up my transaction log ?? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

URGENT !! How to clean up my transaction log ??

Hi there, I am encountering a production problem. SQL 2000 SP3a. Two of my databases’s log were grown to 4GB each and now the drive is having only 100MB free space. I tried dumping the log with no_log and it doesn’t seem to work. Is there any way to clean up the space apart from re-booting the server ?? Thanks & regards,

Run DBCC SHRINKFILE in order to shrink the transaction log file to save the space.
Refer to books online for Error 9002 topic:

The user action that is appropriate to you depends on your situation. Potentially, possible actions
include: Backing up the transaction log
Freeing disk space
Moving the log file to a disk drive with sufficient space
Adding or enlarging a log file
Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
What kind of Recovery Plan are you using for your databases?
This setting is important when it comes to disk space usage, as it can grow endlessly …

If you’re a little bit crazy, you can
detach the db in question
rename or delete the log file
attach the db in question
ignore the error message
That should create a new and fresh log though one might consider it a brute-force-method.
Always!!! be sure, you have a current backup at hand.

I don’t think reboot the system fix the problem.
What recovery plan do you have and how backup your databases?
Luis Martin
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.