DBCC SHRINKFILE on log file not reducing size even after BACKUP LOG TO DISK

Question

I've got a database, [My DB], that has the following info:
SQL Server 2008
MDF size: 30 GB
LDF size: 67 GB

I wanted to shrink the log file as much as possible and so I started my quest to figure out how to do this. Caveat: I am not a DBA (or even approaching a DBA) and have been progressing by feel through this quest.

First, I just went into SSMS, DB properties, Files, and edited the Initial Size (MB) value to 10. That reduced the log file to 62 GB (not exactly the 10 MB that I entered). So, I attached SQL Profiler, saw that DBCC SHRINKFILE was being called. I then entered that command into the query editor and here's the results.

DBCC SHRINKFILE (N'My DB_Log' , 10)

And the output was:

Cannot shrink log file 2 (My DB_Log) because the logical log file located at the end of the file is in use.
DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
8      2           8044104     12800       8044104     12800

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I then did some research on that and found this:

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

Which says that I need to backup the log file before the shrinkfile so that the virtual log files will be released and the shrinkfile can do its job - I don't know what that means... I'm just paraphrasing here :)

So, I figured I'd try to backup the log file and then do a DBCC SHRINKFILE (and I changed the new log file size to 12800 since that was the MinimumSize identified in the output of the previous DBCC SHRINKFILE command)

BACKUP LOG [My DB] TO DISK = 'D:\SQLBackup\20110824-MyDB-Log.bak'
GO
DBCC SHRINKFILE (N'My DB_Log' , 12800)
GO

The result was the same as the first go around. I can only get the log file down to 62 GB.

I'm not sure what I'm doing wrong and what I should try next.

Cloe

in order to prevent this from happening again you should run log backups or set the recovery mode to simple. – Roo

My problem was a PAUSED REPLICATION / MIRRORING setup -- it appears sql won't shrink tlogs if it thinks it'll need them for replication. This probably won't be very many people's problem, but it might help a few. – Behnken

Answer 1

In addition to the steps you have already taken, you will need to set the recovery mode to simple before you can shrink the log.

THIS IS NOT A RECOMMENDED PRACTICE for production systems... You will lose your ability to recover to a point in time from previous backups/log files.

See example B on this DBCC SHRINKFILE (Transact-SQL) msdn page for an example, and explanation.

Alanna

Just out of curiosity, is there a way to see what the recovery mode is by going through the SSMS UI? I looked through DB Properties but did not see it. The closest was the Properties/Options page that shows the Recovery/PageVerify value. – Cloe

Found it - it's not in the list of "Other options". It's at the top of the page - one of the three drop downs. Thx. – Cloe

I was going to suggest running this query: select recovery_model_desc, from sys.databases where name = 'database name' – Alanna

Thanks. Changing the recovery model was the key :) – Amity

Answer 2

Okay, here is a solution to reduce the physical size of the transaction file, but without changing the recovery mode to simple.

Within your database, locate the file_id of the log file using the following query.

SELECT * FROM sys.database_files;

In my instance, the log file is file_id 2. Now we want to locate the virtual logs in use, and do this with the following command.

DBCC LOGINFO;

Here you can see if any virtual logs are in use by seeing if the status is 2 (in use), or 0 (free). When shrinking files, empty virtual logs are physically removed starting at the end of the file until it hits the first used status. This is why shrinking a transaction log file sometimes shrinks it part way but does not remove all free virtual logs.

If you notice a status 2's that occur after 0's, this is blocking the shrink from fully shrinking the file. To get around this do another transaction log backup, and immediately run these commands, supplying the file_id found above, and the size you would like your log file to be reduced to.

-- DBCC SHRINKFILE (file_id, LogSize_MB)
DBCC SHRINKFILE (2, 100);
DBCC LOGINFO;

This will then show the virtual log file allocation, and hopefully you'll notice that it's been reduced somewhat. Because virtual log files are not always allocated in order, you may have to backup the transaction log a couple of times and run this last query again; but I can normally shrink it down within a backup or two.

Overstay

Worked like a charm. Backup 99% free log only took a second, and the file immediately went from over 22,000MB to 200MB. This should be marked as the correct answer. – Bowyer

I appreciate the background info. I'm still stuck, though, because I think your solution boils down to the solution that the original poster tried and couldn't get to work. Is your solution basically to run "BACKUP LOG... GO DBCC SHRINKFILE..." several times until it works? I've tried several times without success, and it seems like the OP tried it also without success. Just wanting to clarify to see if I'm missing something in your answer. – Behnken

UPDATE: my problem was that a paused replication/mirroring setup was locking the tlog. Probably a small edge case. – Behnken

@Behnken yes if you want to shrink the file to the lowest size possible, you have to do a backup, shrink, backup and shrink, all in one go. The reason (I think it is intended), is that the shrink only reduces the size of the log file to the size of the 'used pages' since the last backup; probably to minimize the need to grow the log file, useful for production environment where regular load and backups occur. The above tries to demonstrate and debug this. If you have '0's in your log file, it can be shrunk further. If not, then there may be problems with releasing pages of your backed up logs. – Overstay

Answer 3

Try this

ALTER DATABASE XXXX  SET RECOVERY SIMPLE

use XXXX

declare @log_File_Name varchar(200) 

select @log_File_Name  = name from sysfiles where filename like '%LDF'

declare @i int = FILE_IDEX ( @log_File_Name)

dbcc shrinkfile ( @i , 50) 

Aquarium

This is gold!!! – Hedley

Note that this will destroy the tail log (the transaction logs not yet backed up (aka "redo logs" if you come from oracle), so any transaction log backups made after that and before the next full backup will be unusable. Make a transaction log backup before the ALTER DATABASE and a full backup right after that. – Behan

Answer 4

I use this script on sql server 2008 R2.

USE [db_name]

ALTER DATABASE [db_name] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE([log_file_name]/log_file_number, wanted_size)

ALTER DATABASE [db_name] SET RECOVERY FULL WITH NO_WAIT

Marquita

And, if you do that, you've BUSTED your transaction log backup chain - such that if there's a disaster after you reset to FULL recovery... you'll 100% lose data UNLESS you kick off either a FULL or DIFF backup after resetting to RECOVERY FULL.Also, WITH NO_WAIT means you give in-flight transactions NO time to complete. A better option would be WITH ROLLBACK AFTER 10 SECONDS (or something similar). – Act

How-to get log_file_name or number ? – Hippocampus

Answer 5

Paul Randal has an exccellent discussion of this problem on his blog: http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx

Remora

BACKUP LOG ETL_Log_1 WITH NO_LOG not working – Hippocampus

Answer 6

I tried many ways but this works.

Sample code is availalbe in DBCC SHRINKFILE

USE DBName;  
GO  
-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE DBName  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (DBName_log, 1);  --File name SELECT * FROM sys.database_files; query to get the file name
GO  
-- Reset the database recovery model.  
ALTER DATABASE DBName  
SET RECOVERY FULL;  
GO

Redemption

This is a really bad idea, it is offered in the Currently accepted answer with a disclaimor on why it is BAD! – Ragman

Answer 7

I resolved this problem by taking the full and transactional backup. Sometimes, the backup process is not completed and that's one of the reason the .ldf file is not getting shrink. Try this. It worked for me.

Slavism

Answer 8

One of our heavily transacted databases grows a few hundred thousand records in a log table every day. There are multiple log files that grow a few hundred GB every day.

We have a scheduled job that takes differential backup every half an hour. We have another scheduled job for housekeeping that runs early morning every day.

We do SHRINKFILE during the housekeeping after setting the RECOVERY to SIMPLE. We do take a full backup at the beginning and at the end of the process in order to overcome the issue of losing our ability to recover to a point in time from previous backups/log files. We use some flag in the database to make sure that the differential backup is not attempted until the housekeeping job is completed. The brief outline is as below - The House Keeping job:

1. Set the status to 'Housekeeping in progress'
2. Set the database to Single User mode
3. Take a full backup of the database
4. Delete records from various tables that are old
5. Set the database RECOVERY mode to SIMPLE
6. Iterate through the log files and shrink each of them
7. Set the database RECOVERY mode to FULL
8. Take a full backup of the database
9. Set the database to Multi-User mode
10. Set the status to 'Housekeeping completed'

The differential backup job:

1. Proceed only if the status is 'Housekeeping completed'
2. Take a differential backup

It takes a while to complete but it gets our database tidy and fresh before the regular business starts in the morning. It has been working fine for us.

Wohlen

Answer 9

Had same issue, gave a try with DBCC FREEPROCCACHE.

It did help, I've managed to do shrink on tempdb's data files.

Afternoon

Answer 10

I struggled with this on my database and finally found a comment about the CHECKPOINT call. My database was already set for SIMPLE logging. The key was to run this several times until there were results. I was able to get from about 7GB log file down to around 100 MB. Having a memory optimized file group may have something to do with why this was needed.

CHECKPOINT
GO
DBCC SHRINKFILE (Your_Log_File_Name_Or_Id_Here, 100)
GO

Sidnee

Answer 11

Thanks to @user2630576 and @Ed.S.

the following worked a treat:

BACKUP LOG [database] TO DISK = 'D:\database.bak'
GO

ALTER DATABASE [database] SET RECOVERY SIMPLE

use [database]

declare @log_File_Name varchar(200)

select @log_File_Name = name from sysfiles where filename like '%LDF'

declare @i int = FILE_IDEX ( @log_File_Name)

dbcc shrinkfile ( @i , 50)

ALTER DATABASE [database] SET RECOVERY FULL

Receivership

This is a really bad idea, it is offered in the Currently accepted answer with a disclaimor on why it is BAD! – Ragman