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,
Kanthi

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

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
Moderator
http://www.SQL-Server-Performance.Com/forum
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 …

BACKUP LOG … WITH NO_LOG
DBCC SHRINKFILE
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.
–Frank
http://www.insidesql.de

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

]]>