Shrinking transactional Log file not working

Question

I restored a Production database to test environment. In Prod it's configured to Transactional Replication and database around 400GB, log file alone 120GB.

I tried Database set to Simple recovery and shrink DBCC Shrinkfile still log file size same (I know shrinking is not an ideal solution, but I want to make it small). There are no long running transactions and blocking

Here is what I followed:

* Backup database

    ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
    GO
    DBCC SHRINKFILE (databasenaem_log,5) 
    GO

    ALTER DATABASE DatabaseName SET RECOVERY FULL
    GO

I checked the sys.databases, log_reuse_wait_desc column and it shows "replication", this may be the reason log file won't allow shrinking. The problem is that there is no replication (publisher or subscriber) on the on the Database or server.

select name, log_reuse_wait_desc from sys.databases

Do I need to set up replication and turn off?

Why are you setting the Recovery Model back to FULL? I suspect you don't need point-in-time restores in your test environment. — Alan Burstein, Nov 22 '19 at 01:33
@AlanBurstein, I need to keep DB in FULL recovery after shrinking Log files — CR241, Nov 22 '19 at 01:56

score 0 · Answer 1 · answered Nov 22 '19 at 01:51

This is an issue that I have encountered before. Sometimes it is because Replication was configured and then removed while the replication processes were still running, sometimes the cause isn't known (DBCC CheckDB with REPAIR ALLOW DATA LOSS seems to be a frequent cause), but the best way I ever discovered to fix the issue is with the information in this MSDN article.

Basically, you set up replication with SNAPSHOT replication, then remove the replication.

This will clear the Replication hold on the log, and allow you to shrink it.

Shrinking transactional Log file not working

1 Answers1