How do you clear the transaction log in a SQL Server 2005 database?

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

How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005

Answer 2

Or you could also use a Maintenance Task that does this for you periodically. Create a new maintenance task and the wizard will -at some point- ask you to shrink the Transaction Log. You have a few options to set there.

Schedule that to be executed every morning and your test db will stay clean most of the time.

Edit: in order to schedule a Maintenance Task, you'll need the SQL Agent service to be running.

Answer 3

Here is a simple and very inelegant 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 4

To Truncate the log file:

• Backup the database
• Detach the database, either by using Enterprise Manager or by executing : *Sp_DetachDB [DBName]*
• Delete the transaction log file. (or rename the file, just in case)
• Re-attach the database again using: *Sp_AttachDB [DBName]*
• When the database is attached, a new transaction log file is created.

To Shrink the log file:

• Backup log [DBName] with No_Log

• Shrink the database by either:

  Using Enterprise manager :- Right click on the database, All tasks, Shrink database, Files, Select log file, OK.

  Using T-SQL :- *Dbcc Shrinkfile ([Log_Logical_Name])*

You can find the logical name of the log file by running sp_helpdb or by looking in the properties of the database in Enterprise Manager.

Answer 5

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 6

To my experience on most SQL Servers there is no backup of the transaction log. Full backups or differential backups are common practice, but transaction log backups are really seldom. So the transaction log file grows forever (until the disk is full). In this case the recovery model should be set to "simple". Don't forget to modify the system databases "model" and "tempdb", too.

A backup of the database "tempdb" makes no sense, so the recovery model of this db should always be "simple".

Answer 7

If I need to shrink the files quickly, I

1.first change the recovery model to simple 2.Then the transaction log files goes away 3.Change back the recovery model.

Its a little bit ugly perhaps, but it works for me

Answer 8

To clear a 14GB transaction log file (Backups of transaction log were 11GB and taking ages), With DB Recovery set to FULL as we are cautious with recovery. Why have tranaction logging if you don't recover them in a DR situation?

Using SQL Maint plans & Job Activity Monitor TSQL for DBCCshrinkfile .

In Maint plan

Step 1 - Back up database (Full)

Step 2 - Back up Transaction Log (Truncate set)

Run DB Maint Plan

Then run TSQL Job for the database using cmd: DBCC SHRINKFILE (dbaselogname, 50)

This reduced by half the transaction log size,

Repeated above - reduced transaction log down to 50MB - which is ideal for database size

I have now scheduled to do this early hours every morning to keep the log size down and performance up.

Answer 9

Right click on the database name.

Select Tasks -> Shrink -> Database

Then click OK!

I was actually quite surprised this worked! Normally I've used DBCC before, but I just tried that and it didn't shrink anything so I tried the GUI (2005) and it worked great - freeing up 17Gb in 10 seconds

Edit: In Full recovery mode this might not work, so you have to either back up the log first, or change to Simple recovery, then shrink the file. [thanks @onupdatecascade for this]

Answer 10

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 11

Hi,

I wanted to know that Is the Backup for the transactional log is automatic if my recovery model is Full ? Can I change my transactional log backup frequency through the management studio or bt T-SQL ?

-Aditya

Answer 12

First check the database Recovery model. By default SQL Server Epxress edition create database in Simple recovery model (if iam not mistaken).

Backup Log DatabaseName With Truncate_Only

DBCC ShrinkFile(yourLogical_LogFileName, 50)

SP_helpfile will give you the logical log file name

Refer :

http://support.microsoft.com/kb/873235.

If your database is in Full Recovery Model and if you are not taking TL backup , then change it to SIMPLE.

Answer 13

Depending on your circumstancces, each one of these might or might not be what you actually need to do.

Just go down the list. If one doesn't work, try the next.

Answer 14

Worked for me thanks.

Answer 15

To Truncate the log file:

Backup the database Detach the database, either by using Enterprise Manager or by executing : *Sp_DetachDB [DBName]* Delete the transaction log file. (or rename the file, just in case) Re-attach the database again using: *Sp_AttachDB [DBName]* When the database is attached, a new transaction log file is created.

this worked for me to delete 12gb of junk thnx for the post

Answer 16

Example:-

DBCC SQLPERF(LOGSPACE)

BACKUP LOG Comapny WITH TRUNCATE_ONLY

DBCC SHRINKFILE (Company_log, 500)

DBCC SQLPERF(LOGSPACE)

Answer 17

Exercise caution doing the detach and rename LDF file trick.

It didn't work exactly as written (I couldn't reattach the DB without it), and the database I detached was my default database, so I couldn't log on to the SQL Server anymore. I had to actually go to a buddy's workstation who also had SA rights to sort it out.

After reattaching though, I was able to change "Recovery Type" to "Simple" and shrink the log files.

Answer 18

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 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 on this very topic. http://sqlskills.com/BLOGS/PAUL/category/Bad-Advice.aspx#p4

Also in general do not use shrinkfile on the MDF's 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.