THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Remove transaction log files

This blog has moved! You can find this content at the following new location:

http://sqlblog.karaszi.com/remove-transaction-log-files/

Published Wednesday, June 17, 2009 8:47 AM by TiborKaraszi

Comments

 

Linchi Shea said:

> As always, don't execute it if you don't understand what it does!

My mode of learning is first executing it to see what happenns and then studying it :-)

June 17, 2009 12:05 PM
 

EvilPostIT said:

Is it possible to change the primary log file to be another log file or is this internal?

July 6, 2009 1:15 PM
 

TiborKaraszi said:

I don't think you can change the primary log file. So, if you need to move it, you are probably in for a brief period of off-line for the database (procedure for moving database files is described in Books Online).

July 6, 2009 1:24 PM
 

ffx100 said:

It does not matter what I do, I cannot delete second log file. I am getting error saying "... file is not empty ...". Loginfo shows status 0 for second file, DBCC SHRINKFILE command returns current and minimum sizes = 153.

September 27, 2011 12:03 PM
 

puvy said:

can u explain what this scrip does?

March 15, 2013 1:54 PM
 

rpbos said:

Tibor, I just used your script above in our environment and it worked perfoectly.  Thanks so much for the clear detailed explanation.

May 21, 2013 6:36 PM
 

dan said:

--Shrink 3

USE rDb

DBCC SHRINKFILE(rDb_log3)

DON'T YOU HAVE TO ADD '0' OR EMPTYFILE NEXT TO FILE NAME IN ORDER TO EMPTY FILE?

LIKE: DBCC SHRINKFILE(rDb_log3, 0) or DBCC SHRINKFILE(rDb_log3, emptyfile)

October 19, 2014 8:50 AM
 

Saurabh said:

I used the following and reduced the size of primary log file(.ldf) from 9 GB to 1 MB.

USE TestDB;

GO  

-- Truncate logs by changing the database recovery model to SIMPLE.  

ALTER DATABASE TestDB

SET RECOVERY SIMPLE;  

GO  

-- Shrink the truncated log file to 1 MB.  

DBCC SHRINKFILE (TestDB_log, 1);  

GO

I am going to do this in production as well without 'RECOVERY FULL' or 'BULK-LOGGED'

Is there any harm ? why to bother about making giant .ldf files; which never ever get used.

September 23, 2016 1:15 PM
 

Pradeep said:

Thanks a lot Saurabh for your solution which really works for me...I have been searching Google from couple of days to find the Live example...Finally I see it Here...Thanks a lot for sharing this solution.

July 14, 2017 9:59 AM
 

Jekic said:

Hi Tibor,

Your script isn't good template for removing additional Transaction Log Files. If you skip fake line (line before SHRINKFILE):

--Can't remove 3 since it is in use

ALTER DATABASE rDb REMOVE FILE rDb_log3

, after SHRINKFILE and BACKUP, you will not be able to REMOVE file rDb_log3.

This is because, with this fake line, you move pointer from file rDB_log3 to file rDB_log2. Each time when you run this fake line, you produce moving pointer to next VLFS (or to next part of existing VLF).

August 24, 2017 6:12 AM
New Comments to this post are disabled
Privacy Statement