Because REPLICATION,DBCC shrink cannot shrink the log File

After using Backup to create the test environment, found that the TestDB log file is too large to reach 400GB, because the test environment does not really need such a large log space, occupy 400GB disk space is a waste of disk Resource, Then use DBCC shrink to shrink the log File:

DBCC shrinkfile (testdb_log_5,10240, notruncate)DBCC shrinkfile (Testdb_log_5, 10240, truncateonly)

After the naming execution is complete, it is found that there are more than 300 GB, the actual log file occupies a very low percentage of space, 0.000428%

DBCC SQLPERF (Logspace)

Because the restore mode of test db is simple, and there is no active user, the biggest possibility is that DB's trasaction log is marked as replication, using the following function statistics to find that a large number of logs have not been read by LogReader.

Select Count (0)  from Sys.fn_dblog (null,null) Fwhere='  REPLICATE'

In Publisher database, use Sp_repltrans to view transaction that are not marked as distributed by LogReader.

Sp_repltrans returns a result set of all the transactions in the publication database transaction log that is marked for Replication but has not been marked as distributed.

exec Sys.sp_repltrans

Unable to execute procedure. The database is not published. Execute the procedure in a database, published for replication.

Because TestDB is a test database that is restored using backup and is not registered as publisher database in master, the database must be set to publish, which indicates that the database can be used for other types of publications.

exec sys.sp_replicationdboption         @dbname = n'testdb',         @optname= n'Publish  ',         @value= N'true'

After successful registration, use Sp_repldone to mark all transaction logs as distributed.

sp_repldone updates the record that identifies the last distributed transaction of the S Erver.

 exec   Sys.sp_repldone   @xactid  =  null  ,   @xact_segno  =  null    @numtrans  =  0  ,  
    
      @time 
     =  0  Span style= "color: #000000;" >,   @reset  =  1  

When Xactid was null, xact_seqno is null, and reset was 1, all replicated transactions I n the log is marked as distributed. This was useful when there was replicated transactions in the transaction log that was no longer valid and you want to Trun Cate the log,

Finally, using the DBCC SHRINKFILE command, the Transaction Log file shrinks to completion.

Reference doc:

SP_REPLTRANS (Transact-SQL)

sp_replicationdboption (Transact-SQL)

SP_REPLDONE (Transact-SQL)

Because REPLICATION,DBCC shrink cannot shrink the log File