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.
Best Answer
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 inSIMPLE
recovery mode so a backup is not needed, that's forFULL
andBULK 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.