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.

Solution 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.

Solution 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.

Solution 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) 

Solution 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

Solution 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

View more solutions

Comments

[DForck42]

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

[pettys]

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.

[Ed Sinek]

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.

[Ed Sinek]

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.

[jlnorsworthy]

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

[Triynko]

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.

[pettys]

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.

[pettys]

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

[Radderz]

@pettys 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.

[Michael K. Campbell]

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).

[Alireza]

Thanks. Changing the recovery model was the key :)

[James Jenkins]

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

[James Jenkins]

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

[Kiquenet]

How-to get log_file_name or number ?

[Kiquenet]

BACKUP LOG ETL_Log_1 WITH NO_LOG not working