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.

Flyover

possible duplicate of Why Does the Transaction Log Keep Growing or Run Out of Space? – Proulx

If you need full recovery, you can't shrink the log file without backing it up. If you were able to truncate the log and did so, it would not be possible to do point-in-time restores. – Thissa

You can temporarily set recovery model to simple and truncate log You also need to find the long running transaction that is active and find the root cause You can see here blog.sqlxdetails.com/… – Matisse

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/

Matisse

Is it 100% safe to truncate log? Can I loose uncommited transactions? – Flyover

@Ricardo, it is unsafe and breaks transaction chain – Matisse

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 – Matisse

Answer 2

With the help of below command you can clear transaction log file. command is well commented.

-- see the log size
DBCC SQLPERF(LOGSPACE);

--taking backup for log file before shrink it
BACKUP LOG MyTestDB
TO DISK = 'E:\PartProcForOld_log_backup\MyTestDB.TRN'
GO

-- this command will tell you the log file name
SELECT name
FROM sys.master_files
WHERE database_id = db_id()
  AND type = 1


--- these below command will alter database with actual shrink
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE MyTestDB

SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (MyTestDB_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE MyTestDB
SET RECOVERY FULL;
GO

Slone

"without backuping first" – Boatwright

Answer 3

Below script works without taking LOG backup as well. Make sure no one accessing this database.

ALTER DATABASE databasename SET RECOVERY SIMPLE

go

USE databasename

GO

DBCC SHRINKFILE (N'mydatabase_Log' , 0, TRUNCATEONLY)

GO

USE master

GO

-- Switch to full recovery mode

ALTER DATABASE databasename SET RECOVERY FULL;

GO

Folger