Consolidate two physical log files to one.

Question

Post reply

Consolidate two physical log files to one.

Martin Vrieze

SSCrazy

Points: 2760
More actions
May 18, 2004 at 9:03 am

#85112

I have a database that has two physical log files in which transactions are being logged. One is very small and is hardly ever used by the database for logging.
I would like to remove this second log file entirely but SQL Server will not let go.
Has anyone run into this and performed this task? If so, how?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Terry L Broadbent Valued Member Points: 67 More actions · Answer 1

Backup the database or truncate the transaction log. Run the following statements.

DBCC SHRINKFILE ('<logical file name>', EMPTYFILE )

ALTER DATABASE <Database name> REMOVE FILE <logical file name>

Do a full backup immediately.

TL Broadbent

Martin Vrieze SSCrazy Points: 2760 More actions · Answer 2

I ran the following...

DBCC SHRINKFILE (RECOMMEND_Log2, EMPTYFILE )

ALTER DATABASE RECOMMEND REMOVE FILE RECOMMEND_Log2

GO

SQL Server returns the following...

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Server: Msg 5042, Level 16, State 2, Line 3

The file 'Recommend_Log2' cannot be removed because it is not empty.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

SQL Server will move through the log files and log to one, then the other, using virtual log files within each physical file. You need to add some transactions, truncate the log and try again. If it doesn't work repeat. Eventually you'll use up the space and commit the transactions in the small file and you'll be able to remove it.

Terry L Broadbent Valued Member Points: 67 More actions · Answer 4

Did you perfrom a full backup or a log backup with the truncate_only optionprior to running teh shrinkfile command?

TL Broadbent

Martin Vrieze SSCrazy Points: 2760 More actions · Answer 5

Martin Vrieze

SSCrazy

Points: 2760

May 18, 2004 at 11:29 am

#506830

WITH TRUNCATEONLY...

Markus Veretin SSCommitted Points: 1565 More actions · Answer 6

Use db

GO

DBCC shrinkfile (db_Log1, notruncate)

DBCC shrinkfile (db_Log1, truncateonly)

Create table t1 (char1 char(4000))

GO

Declare @i int

Select @i = 0

While (1 = 1)

BEGIN

While (@i < 100)

Begin

Insert into t1 values('a')

Select @i = @i + 1

End

Truncate table t1

Backup log db with truncate_only

End

drop table t1

Martin Vrieze SSCrazy Points: 2760 More actions · Answer 7

I ended up shrinking the transaction log for this database in order to force the transaction log to fill up entirely (as suggested by Steve Jones).

Once the transaction log was completely full, I expanded the size of the transaction log in order to make space for the contents of the transaction file that was about to be removed. Once expanded, I reissued the following as suggested by "newbie"...

DBCC SHRINKFILE (RECOMMEND_Log2, EMPTYFILE )

ALTER DATABASE RECOMMEND REMOVE FILE RECOMMEND_Log2

GO

The small transaction log file emptied into the larger one and the smaller one was removed successfully.

Thanks everyone!!!