SQL 2008 shrink log file size with no_log alternative

Microsoft SQL Server 2008 does not allow you to truncate the transaction log using the no_log syntax.


Use the below code to shrink the log file for your database


USE MASTER
GO
ALTER DATABASE DB_Name SET RECOVERY SIMPLE
GO
USE DB_Name
GO
DBCC SHRINKFILE (DB_Name_log, 1)
GO
ALTER DATABASE DB_Name SET RECOVERY FULL
GO


SQL 2008 returns a table of shrinkfile related results plus the following confirmation:


(1 row(s) affected)
DBCC execution completed. If DBCC printed error
messages, contact your system administrator.

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