How do you clear the SQL Server transaction log?

Question

I'm not a SQL expert, and I'm reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log?

Answer 1

Here is a simple and very inelegant & potentially dangerous way.

1. Backup DB
2. Detach DB
3. Rename Log file
4. Attach DB
5. New log file will be recreated
6. Delete Renamed Log file.

I'm guessing that you are not doing log backups. (Which truncate the log). My advice is to change recovery model from full to simple. This will prevent log bloat.

Answer 2

If you do not use the transaction logs for restores (i.e. You only ever do full backups), you can set Recovery Mode to "Simple", and the transaction log will very shortly shrink and never fill up again.

If you are using SQL 7 or 2000, you can enable "truncate log on checkpoint" in the database options tab. This has the same effect.

This is not recomended in production environments obviously, since you will not be able to restore to a point in time.

Answer 3

Some of the other answers did not work for me: It was not possible to create the checkpoint while the db was online, because the transaction log was full (how ironic). However, after setting the database to emergency mode, I was able to shrink the log file:

alter database <database_name> set emergency;
use <database_name>;
checkpoint;
checkpoint;
alter database <database_name> set online;
dbcc shrinkfile(<database_name>_log, 200);

Answer 4

USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

From: DBCC SHRINKFILE (Transact-SQL)

You may want to backup first.

Answer 5

This technique that John recommends is not recommended as there is no guarantee that the database will attach without the log file. Change the database from full to simple, force a checkpoint and wait a few minutes. The SQL Server will clear the log, which you can then shrink using DBCC SHRINKFILE.

Answer 6

Use the DBCC ShrinkFile ({logicalLogName}, TRUNCATEONLY) command. If this is a test database and you are trying to save/reclaim space, this will help.

Remember though that TX logs do have a sort of minimum/steady state size that they will grow up to. Depending upon your recovery model you may not be able to shrink the log - if in FULL and you aren't issuing TX log backups the log can't be shrunk - it will grow forever. If you don't need TX log backups, switch your recovery model to Simple.

And remember, never ever under any circumstances delete the log (LDF) file! You will pretty much have instant database corruption. Cooked! Done! Lost data! If left "unrepaired" the main MDF file could become corrupt permanently.

Never ever delete the transaction log - you will lose data! Part of your data is in the TX Log (regardless of recovery model)... if you detach and "rename" the TX log file that effectively deletes part of your database.

For those that have deleted the TX Log you may want to run a few checkdb commands and fix the corruption before you lose more data.

Check out Paul Randal's blog posts on this very topic, bad advice.

Also in general do not use shrinkfile on the MDF files as it can severely fragment your data. Check out his Bad Advice section for more info ("Why you should not shrink your data files")

Check out Paul's website - he covers these very questions. Last month he walked through many of these issues in his Myth A Day series.