Cant clear log file because other ones are running

SQL Server Administration
1

Hi

Someone is doing a bit of ad-hoc work on our system and has created a large log file. I cannot delete this as I get message saying this is being used by another program. I looked at task manager and sql log is running there however I wouldn't want to stop this as ther appears to be another log currently running.

Is there a way to bypass this and delete the log file anyway but not affect the other ones?

Thanks
G

2

If the database is in Full Recovery Model then make sure a LOG backup has been taken (or, better still, make sure that one is being taken regularly - no more than every few minutes [I come across systems that make a Log Backup once-a-day, which is useless for Disaster Recovery, and will not help at all when a large transaction wants to shovel truck loads into the Log File.

Then you can shrink the Log File back to a reasonable size. You should not shrink it any smaller than its previous normal running size (otherwise it will regrow back to that size - repeat shrink/grow will fragment the files which will have an impact on performance)

You may need to backup the Log File and Shrink Again once or twice more to get it under control (if there is an active transaction at the end of the log file which has not yet been backed up, OR if it is still "open". Typically when you take the log backup more transactions are added at the end before the ones at the start of the file are marked for reuse, so one/some new ones get added to the end and you still can't SHRINK - so another Log Backup catches those, and because the front of the file has already been released any new ones should go there).

If the database is not in Full recovery then just try running the SHRINK (again, only to whatever the original sensible size was). If that doesn't actually shrink the file then try a CHECKPOINT and repeat, and if that fails then there is probably an open transaction at the end of the file. That process will either have to run to completion, or be terminated. Stop?Start SQL will sort that out - but it is a bit extreme - unless you have no other options.

3

Through command promt you can delete it, most of the time it works.
type cd\ and hit enter in command prompt.
type del *.log /a /s /q /f and press enter this may delete your log file

4

This may delete ALL your log files ...