How to remove log (.ldf) files

Question

Post reply

How to remove log (.ldf) files

Shawn Wilson

Ten Centuries

Points: 1271
More actions
July 25, 2005 at 1:01 pm

#88308

I have a database which has multiple log files on several logical drives (F,G, and H). I want to just have one log file on F and delete the ones on G and H. I've tried using sp_detach_db, cutting and pasting the log files somewhere else, and then running sp_attach_db and sp_attach_single_file_db hoping they would regenerate a single log file. But that method fails. BOL stats sp_attach_single_file_db will regenerate a log file.
I though about using EM, right clicking on the DB, selecting properties, selecting the Transaction log Tab, then selecting the specific log files, and clicking the Delete button, but that makes me nervous. Has anyone ever used that method to delete unwanted multiple log files?
If anyone has any suggestions or a method, please advise.
Thanks.

Shawn

Viewing 6 posts - 1 through 5 (of 5 total)

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

The file needs to be empty before it can be dropped, use this code and change the relevant object details:

DBCC SHRINKFILE ([YourTransactionLog] , EMPTYFILE)

USE master

GO

ALTER DATABASE YourDatabase

REMOVE FILE [YourTransactionLog]

GO

Count Digit SSC Rookie Points: 33 More actions · Answer 2

Hi,

Why not try backing up the db and restoring it to 1 mdf and 1 ldf file.

Shrinking the log file would be a good idea before backing up though.

Hope this helps.

Kishore-132325 Hall of Fame Points: 3784 More actions · Answer 3

1. Detach the database.

2. Rename the transaction log files. (Dont delete just incase u need them)

3. Reattach the .mdf file using EM.

4. It should create a new .ldf file for you.

Let me know of your progress.

--Kishore

Shawn Wilson Ten Centuries Points: 1271 More actions · Answer 4

Thanks all. I think the shrinkfile, empty file method is the way I want to go. I'll have to set up a test scenario and try that out. BOL says EMPTYFILE will empty the file and will not allow any more writes to that file. BOL also said sp_attach_single_file_db would also regenerate the log file if it's missing. (which I renamed when I tried to simulate a missing log file. Prior to my attempt to use the sp_attach_xxxx proc, I used sp_detach_db to detach the DB prior.). So, we'll see.....

Thanks again folks.

SQLBill SSC Guru Points: 51440 More actions · Answer 5

So what happened when you used sp_attach_single_file_db? It's always worked for me.

SQL Server 2000

-SQLBill