SQL Server 2005 Transaction Log will not Truncate

Question

I have a database .mdf file that is 138 GB along with a transaction log file of 55 GB.

The recovery model was set to Full (which it doesn't need to be). I ran a full backup of the database and the transaction log. The transaction log is still 55 GB with no free space to shrink the file.

I ran that backup through SQL Server Management Studio GUI. I then ran the following commands to try to force the trans log to shrink:

BACKUP LOG database WITH TRUNCATE_ONLY
DBCC SHRINKFILE (logfile, TRUNCATEONLY )

The log file is still 55 GB. I then changed the recovery model to Simple and let it sit a few days, but it's still at 55 GB. I tried those 2 commands above again but it still doesn't truncate anything.

No matter what I try, this log file won't shrink. Since we don't need the transaction log at all, I even tried detaching the database, renaming the log file and reattaching. That doesn't work either as there are actually 2 transaction logs, and I get an error when trying to reattach without the logs. The other log file is only 1 MB, and I've tried to remove that as well, but get also get an error saying it's not empty.

Is there something I'm missing, or something else I can try?

Thanks for any help!

Answer 1

In the rare cases where I have to shrink a transaction log, I use a slightly different command on SQL Server 2005 than the one you tried:

backup log database with no_log
dbcc shrinkfile (logfile,1)

(...with no_log instead of ...with truncate_only, and the second parameter of dbcc shrinkfile must be the desired new size of the log file)

To make sure that I get the name of the log file right (and because I'm too lazy to type the name of the database), I use this script that gets the names of the database and the log file automatically:

declare @dbname varchar(255)
declare @logfile varchar(255)

select @dbname = db_name()
select @logfile = name from sysfiles where filename like '%.ldf'

backup log @dbname with no_log
dbcc shrinkfile (@logfile,1)

You need to run it directly in the database of which you want to shrink the log file.
Disclaimer: I never used it on a database that has more than one log file, like yours. Maybe you have to change it if it finds the wrong log file by default.

You can set the recovery mode to Simple before you run the script if you don't need Full recovery mode anyway.

And you should take a full backup immediately after shrinking the log.
This is very important if you leave the database in Full recovery mode!
(because shrinking the log breaks the log chain, which means that subsequent log backups are useless until you do the next full backup)

Answer 2

Try setting a checkpoint http://msdn.microsoft.com/en-us/library/ms188748.aspx

This will write all dirty pages to disk, flushes log pages from the buffer cache of the current database, minimizing the number of modifications that have to be rolled forward during a recovery. It creates a new minimum recovery log sequence number (lsn) and deletes all records before that lsn, shrinking the file.

Answer 3

"I even tried detaching the database, renaming the log file and reattaching": The log is not an optional component! Run DBCC CHECKDB immediately to see if you have caused corruption. You will cause corruption if there were unflushed pages for some reason. Even if not you are gambling.

The SQL Server log file is not an informational text log.

If you don't need the log, switch to simple mode and shrink the log file (you already did that). TRUNCATE_ONLY is obsolete, I don't know if it even does anything.

Look at sys.databases and look at the value in the log_reuse_wait_desc column. It will tell you what is keeping the log around.

Answer 4

First, can you set the initial size of the log file lower? If someone started it at 55GB, it may not go lower than that.

Second, you probably don't need two transaction logs. The second one will be "active," but not used until the first one is full. The first one may never get full if you don't have restricted growth and you still have disk space left.

Third, as @JohnNolan suggested (and as I was about to suggest), try a checkpoint, since that should allow a log truncation under the simple recovery model.

Also, it is recommended that you avoid using TRUNCATE_ONLY.

Answer 5

it has been an issue with ms sql servers but here's the solution....

to force truncate the database do the ff:

1. run full db backup prior to shrinking
2. set recovery from full to simple
3. dbcc shrinkfile
4. set recovery back to full
5. run full db backup after shrunken

here's the code for force shrinking

 use master
 Alter database dbname set Recovery simple; 

 use dbname
 DBCC SHRINKFILE (dbname_log, 0, TRUNCATEONLY)
 Alter database dbname set Recovery full;

hope this helps.

Answer 6

How I'd troubleshoot this:

1) select log_reuse_wait_desc from sys.databases where name = 'your_db_name'

This will give you what's preventing VLFs from being reused. During slow periods (i.e. when the log isn't being used much, this should be 'NONE'). You may need to troubleshoot if you see another value.

2) Take a look at the output of dbcc loginfo. It returns one row for each VLF that you have in your log file. The status column tells you whether the VLF is in use or not. In order to shrink the log, you need the unused VLFs to be at the end of the file. You may have to wait for natural activity to do this. Since VLFs get used in a kind of ring, the unused one(s) should eventually be at the end of the file.

Answer 7

Use below query:

backup log [dbname] with truncate_only
go
DBCC SHRINKDATABASE ([dbname], 10, TRUNCATEONLY)
go