2 ldf files to one ldf file

Question

2 ldf files to one ldf file

rpatil22

Ten Centuries

Points: 1371
More actions
July 1, 2009 at 9:02 am

#133502

i have a db having 2 logs files on different drive e:\ & f:\
d:\machine.mdf
e:\machine_log.ldf
f:\machine_log2.ldf
Now since new disk space added to e:\ , we want move both log files to one drive.
so i m doing detach and attach.
d:\machine.mdf
e:\machine_log.ldf
e:\machine_log2.ldf
is it possible if i attach only one log file ?
d:\machine.mdf
e:\machine_log.ldf

Viewing 10 posts - 1 through 9 (of 9 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736557 More actions · Answer 1

No, don't do that. The system expects two log files. Attach both, then try to use SSMS to remove the 2nd one. IF it doesn't work, try a shrinkfile to shrink the 2nd one to 0 bytes, then remove it.

Eswin SSCrazy Eights Points: 8320 More actions · Answer 2

can we append the backup of both tran logs and restore to one single log.

Tanx 😀

ps. One Orange Chip Points: 29423 More actions · Answer 3

I dont think you can control individual log files. log files are written in a round-robin fashion. when one file gets full, sql server moves to another file for logging transactions.

Pradeep Singh

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 4

Here is your answer for both SQL Server 2000 and 2005 :

backup log test with no_log <-- take the tail log backup .

dbcc shrinkfile (test_log2,emptyfile) <-- shrink it and empty it .

alter database test remove file test_log2 <-- remove it .

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 5

Pradeep its not the round robin algorithm that SQL uses any more .Perhaps it would have been in the earlier builds .But 2000 onwards we use proportional fill algorithm i.e. all files will be filled in such a way that they fill together ..

Regards

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

ps. One Orange Chip Points: 29423 More actions · Answer 6

i was of the opinion that data files get filled proportionally and log files in round robin sequence. I cant seem to find related articles...

Pradeep Singh

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 7

Actually you are right .T-Logs DO NOT work on the principal of porportional fill...

I apologize for my previous mail .....

Regards

Abhay

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

ps. One Orange Chip Points: 29423 More actions · Answer 8

😉 Never mind. we all do get confused in most basic things at times.

Pradeep Singh

red_serene SSC-Addicted Points: 433 More actions · Answer 9

Have you thought of the option of shrinking all of the data out of one of the transaction log files (see DBCC SHRINKFILE) so that it is empty, and then using the ALTER DATABASE command to remove the log file that was just emptied? In that way, you combine the 2 log files into a single file and then end up with one *.MDF and one *.LDF file for the database.