Search code examples
sql-servershrinkdbcc

Log file is not Shrinking on SQL Server

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.

Solution

  • 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.