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

1 Answer 1

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

Your Answer

 
discard

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