Log file is not Shrinking on SQL Server

1
votes

I wanted to shrink the log file as much as possible by the command:

DBCC SHRINKFILE ('LogFile', 10)

and get this error:

Cannot shrink log file 2 ('LogFile') because the logical log file located at the end of the file is in use.

I tested these solutions, but my problem isn't solved yet

  • DBCC OpenTran => No active open transactions.
  • Change Recovery Model and DBCC ShrinkFile
  • Log Backup AND DBCC ShrinkFile

How can I solve this problem?

SOLVED

I executed this query 

SELECT name, database_id, log_reuse_wait, log_reuse_wait_desc FROM sys.databases

Result:
log_reuse_wait_desc: CHECKPOINT

and after execute CHECKPOINT and DBCCSHRINKFILE my problem solved.

1
sql-servershrinkdbcc
What does this show? SELECT name, database_id, log_reuse_wait, log_reuse_wait_desc FROM sys.databases - manderson
log_reuse_wait_desc equal to 'CHECKPOINT' - M. Rezaeyan
Looks like there was some success here. sqlservercentral.com/Forums/Topic122690-24-1.aspx - manderson
I executed CHECKPOINT & DBCC SHRINKFILE, and my problem solved. - M. Rezaeyan

1 Answers

0
votes

I executed this query

SELECT name, database_id, log_reuse_wait, log_reuse_wait_desc FROM sys.databases

Result: log_reuse_wait_desc: CHECKPOINT

and after execute CHECKPOINT and DBCCSHRINKFILE my problem solved.