Restore database and transactions logs

Question

There is a production database ~1T, and ~200GB of log file unused space, simple recovery mode. I want it to restore at test environment and get rid of those 200GB log file to free up space for other DBs restores. What I tried:

Backup DB at production

Kill connections and restore DB (with recovery and replace) at test target VM

Clean up replication of restored database

Shrink DB DBCC SHRINKFILE (N'MyLogFile',1) of restored database

The process above is automated, so I can't run many checks during the process.

The step 4 fails with an error: Cannot shrink log file 2 (MyLogFile) because the logical log file located at the end of the file is in use.

Questions:

Can this be because the database has transactional replication enabled, although I clean up replication at step 3.

Do I actually need shrink of the logs, may would be enough just leave it after restored, and simple recovery mode checkpoints will do log backups (and decrease log file unused space) automatically?

Answer 1

If you want the file to be smaller, you need to shrink it. (Or, God forbid!, enable autoshrink.)

I would try adding a few CHECKPOINT to the script. Maybe also a WAITFOR.

Answer 2

Hi @Rana Arsalan ,

Yes, you need to shrink log file if you want to reduce the size of log file. Simple recovery mode checkpoints cannot reduce the size of log file.

if the transaction log cannot be shrank, please execute the following statement to check why the transaction log cannot be shrink:

SELECT log_reuse_wait_desc FROM sys.databases WHERE name='<database name>'  

if the result is other than NOTHING, please perform corresponding operation.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".