unable to shrink log file of database in SQL server due to log_reuse_wait_desc shows replication

Issue : When you try to shrink the database log file , you are unable to shrink

try to investigate the column select log_reuse_wait_desc,* from sys.databases

if the column shows replication for the referred database name , below are the observations to be made

1, Is replication really configured on the database (the reason i said this is because some times database is restored from backup file , and the backup file is of the database on source server where replication is configured )

2, replication is configured (i.e database is publisher and there observes to be replication issue in launch replication monitor for the specified publication)

3, CDC (change data capture) is enabled for the database and the job cdc.dbname_capture isn't running or in an bad state


Case I :  The database log_reuse_wait_desc column shows replication and there wasn't any replication configured on the server

later I observed the database was restored form the backup file , which was of the database (db_a) which has replication configured on server a (servera is source server where db_a database exists and acts as a publisher)

Steps taken to fix the issue :

Use [provide dbname where the issue is observed]

EXEC sp_repldone @xactid = NULL,
  @xact_segno = NULL, @numtrans = 0,
  @time= 0, @reset = 1

sp_removedbreplication [provide dbname where the issue is observed]

Case II : Database was configured with CDC (Change data Capture) and observed job status through sql server agent suggests cdc.dbname_capture jobs was idle and last run outcome was failed.

restarted the job and  soon available free space in log file of database increased (thus shrinking of log file is possible now)

note : cdc.dbname_capture job is expected to be running continuously or at specified intervals , based up on the requirement or business needs for CDC configuration

** for checking database log file free space and shrinking the log file to release back the space to OS

Run the following code to see the free space of the database files (all data and log files might appears)

USE  [provide dbname where the issue is observed]

select
a.FILEID,
[FILE_SIZE_MB] =
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,50),
FILENAME = left(a.FILENAME,100)
from
sys.sysfiles a

** If above code shows log file of database has more than 60% free space , proceed with shrinking log file by code

USE  [provide dbname where the issue is observed]
DBCC shrinkfile ('logical_name_of_file',size_in_MB)

--size_in_MB is the amount of space that to be left in the file after shrink finishes