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
- There was another database which is waiting log backup to release the space
- 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