How to shrink a log file without backuping first?

Question

I have a SQL Server 2008 database with a .mdf file with 1 GB and a .ldf file (log) with 70 GB. I really don't know what took my log file to be so big in a week and to stop to increase, but my main issue is to fix this problem.

I'm used to reduce the log file shrinking it, but I can only shrink IF I backup it first. If I try to shrink without backuping first (using SSMS), nothing happens, even with SSMS showing that available free space is big. I can try shrinking many times but it will work only if I backup first.

The problem is that I can't backup it this time because I don't have free space (the total size of my HD is 120 GB).

Note 1: my database is set to use the full recovery model because I need to be able to do point-in-time recoveries.

Note 2: I know that shrink increases the index fragmentation. After shrinking, I can use REBUILD in indexes to avoid this.

Answer 1

You can temporarily set recovery model to simple and truncate log

you will lose point-in-time recovery ability in time period between last successful log backup and end of the next differential backup that you can take after log cleanup. But point in time recovery possible after backup time onwards

You also need to find the long running transaction that is active and find the root cause

You can see here http://blog.sqlxdetails.com/transaction-log-survival-guide-shrink-100gb-log/