It is interesting to fix one of major database issue which causing a problem to reduce Transaction Log file size from 42 GB to 100MB.
Database file (MDF) 760MB
Database Log file (LDF) 42GB
-- STEP 1
DBCC Shrinkfile ('Adventuredb_Log', 100)
GO
-- not working above statement as we expected
I took database backup followed by Transaction Log backup then again tried not working neither T-SQL nor SSMS.
What a day
DBCC Shrinkfile ('Adventuredb_Log', 100)
GO
-- STEP 2
DBCC OPENTRAN()
Transaction information for database 'Adventuredb'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (73283:414:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
-- STEP 3
select log_reuse_wait_desc,* from sys.databases where name = 'Adventuredb'
log_reuse_wait_desc
REPLICATION
EXEC sp_removedbreplication 'Adventuredb'
--Commands completed successfully.
select log_reuse_wait_desc from sys.databases where name = 'Adventuredb'
log_reuse_wait_desc
NOTHING
Finally i have managed shrink the log file to 100MB
