dbcc shrinkfile not reducing size of log file in sql server 2008

Question

I am shrinking log file of SQL Server 2008 R2 database, but it is not working. I was using this code:

ALTER DATABASE [database] SET RECOVERY SIMPLE
DBCC SHRINKFILE(DATABASE_LOG, 20)
ALTER DATABASE [database] SET RECOVERY FULL

I have seen this article on stackoverflow, and tried the following code as per this post, but this also did not solve my problem.

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

Please help.

Answer 1

First do a DBCC LOGINFO(); then go to the very last row. Is the status 2? That means it's active and it cannot be shrunk until that VLF has released that portion of the log file so next checkpoint it could flush it to disk (assuming it has resources).

If it is active you can do a select name, log_reuse_wait_desc from sys.databases to see what is using it. Sometimes it's mirroring, another HA solution, a long transaction, etc.

You can then do a transaction log backup and checkpoint; to force the log file to roll over to the first VLF. Actually you're in SIMPLE recovery mode so a backup is not needed, that's for FULL and BULK LOGGED only.

Once it's rolled over and it is no longer an active VLF, you can remove it. Note that it will remove all the non active VLFs until it hits an active one. At that point it will stop. You'll have to roll over again.

Now would be a great time to brush up on VLF fragmentation and management so you optimize your performance and time to recovery. While were at it, here's some other great info.

Answer 2

Do you get any messages or errors when you run the shrinkfile command?

Usually when I can't shrink a log file, it's because the database was so busy that a transaction started between the log backup and the shrink command. Make sure that you are the only user on the database.