CODEWITHSUNDEEP
sqlsql-server-2008-r2ssms
Ricardo
Ricardo

Reputation: 468

How to shrink a log file without backuping first?

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.

Upvotes: 6

Views: 24941

Answers (3)

Rami Reddy
Rami Reddy

Reputation: 21

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

Upvotes: 1

Monojit Sarkar
Monojit Sarkar

Reputation: 2451

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

Upvotes: 4

radar
radar

Reputation: 13425

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/

Upvotes: 6

Related Questions