Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Mar 26 '14 at 6:57

This question came from our site for professional and enthusiast programmers.

    
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. –  Rafael Piccinelli Feb 6 at 16:45

2 Answers 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.

share|improve this 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 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.