Shrink Log File

Just a few days back I received an issue regarding disk size is out of space. I have tried to shrink the database using DBCC ShrinkFile but there isn’t any success.

I have follow below steps to resolve this issue and it really works.

I have check the below query for

Select name,log_reuse_wait_desc from sys.databases

1. There was another database which is waiting log backup to release the space
2. There was one database who is waiting for Replication to release the space

Database who is waiting for log backup to release the log space

I have taken the log backup two times and executed the database shrink file command to release the space and it has reclaimed 40GB space from transaction log file.

Database who is waiting for Replication to release the log space

This database is in Simple recovery mode and there isn’t any replication enable on this. I have executed the DBCC OPENTRAN command to see any active transaction. I have executed the DBCC OpenTran and it has provided me the below result.

Oldest active transaction:

SPID (server process ID): 101

UID (user ID) : -1

Name : INSERT

LSN : (999:138204:2)

Start time : OCT 13 2009 1:34:47:827PM

SID : 0x88d52e4051a71143adee5dc7b6619f8a

Replicated Transaction Information:

Oldest distributed LSN : (890:2091888:1)

Oldest non-distributed LSN : (896:2784855:1)

I don’t know the exact reason what happened internally. But from the output it seems that there is unmark distributed transaction. So I have executed Sp_Repldone command to unmark the LSN

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

Than I have executed the DBCC ShrinkFile command and it has reclaimed the 400GB space