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

Advertisements

3 thoughts on “Shrink Log File

    1. Jugal Shah Post author

      nope!!! Shrinking the data file will increase the fragmentation and log file again depends… you have to check for the backup policy, by default log backup will truncate the log and control the log file size… let me know if you need more information.

      Reply

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s