SQL Server database log cannot shrink processing process

Today, according to the usual method of shrinking a test database log, found unable to shrink!

dbcc sqlperf(logspace)

Use [Dbname]go ALTER DATABASE [dbname] SET RECOVERY simple with no_waitgodbcc shrinkfile (N ' Dbname_log ', 0, truncateonly) GODBCC shrinkdatabase (N ' dbname ') GO

The log size is still the same.

DBCC Opentran to see if the transaction is open. Find open transactions, perform checkpoints and then shrink, or not!

DBCC Opentran (dbname) CHECKPOINT

Reference: http://bbs.csdn.net/topics/350199751, log transaction marked as distributed, and then contracted, no!

--All replicated transactions in the log will be marked as distributed exec sp_repldone @xactid = null, @xact_segno = null, @numtrans = 0, @time = 0, @reset = 1

　　

Now look at the virtual log files and discover that there are many and are active

DBCC LOGINFO

　

The most important statements are forgotten, which allows you to see a description of the log space reuse waiting for the last checkpoint: Found to be replication.

SELECT Name,log_reuse_wait_desc from sys.databases where name= ' dbname '

This database was previously synchronized as a thing, but it was deleted. The distribution library also does not exist, and now even have a log waiting?!

Refer to http://bbs.csdn.net/topics/390674731?page=1, execute delete all replicated objects.

--Delete all replicated objects in the current database exec sp_removedbreplication dbname

Then look at the log reuse status, normal! It seems to work!

在收缩数据库，查看已经收缩了！其他也恢复正常！

DBCC shrinkfile (N ' Dbname_log ', 0, truncateonly) godbcc loginfogodbcc Opentran (dbname) GO

This shrinkage method is useful when you publish a database subscription synchronization, and normally, you can shrink the log.

Turn from: 41865073

SQL Server database log cannot shrink processing process