3

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.

1
  • try to do it by interface. 'right click on the table' > 'tasks' > 'Shrink' > Files... Set 'File type' to LOG . Check "avaliable free space" this is the space that you can "shrink". if it is a small percentage, Shrink Log will not help you.
    – Racer SQL
    Feb 6, 2015 at 16:45

2 Answers 2

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.