February 22, 2006 at 8:25 pm
I'm in need to flush out the logs for a database by detach, rename ldf and reattach the database but I can't do it because I have two LDF files. The second LDF file is just a 1MB file that I cannot remove with all the research I could find.
Does anyone have any ideas as to how I can merge these two LDF files together? Thanks.
February 22, 2006 at 8:50 pm
I believe DBCC SHRINKFILE() with the EMPTYFILE option should do the trick. Refer to BOL for more info.
February 22, 2006 at 8:52 pm
Hi Paul. Thanks for the quick reply. Can you give me more specific instructions? What's BOL?
The second LDF file's small and there shouldn't be anything in there. I just want to rid of it. Thanks.
February 22, 2006 at 9:28 pm
I just tried it after looking it up on MSDN but I'm still getting the message:
The file 'Log1' cannot be removed because it is not empty.
Log1 = .ldf file name.
February 22, 2006 at 10:09 pm
BOL stands for Books OnLine. You can access it through Query Analyser (QA) - Help Menu/[Transact SQL Help].
Try setting the database to SIMPLE recovery mode, execute CHECKPOINT and then execute DBCC SHRINKFILE([file_id]) with no option. Make sure that you are in the correct database context (thru 'USE [Database Name]'). Then try dropping the file.
February 22, 2006 at 10:18 pm
I've tried this in simple recovery mode and executed checkpoint a few hours ago but it's failing because there's no more drive space on the log drive.
This is basically my situation here:
The log file on G: drive has grown to 30GB which filled it up.
Under pressure, I created another log file on another drive that has much more space in hopes that I can get the DB to continue logging and complete the jobs and enabling other programmers to continue writing to other DBs.
As it turns out, this crippled my ablitity to resort to unmounting the database, rename the .ldf file, then remount the database so a new .ldf can be written. I am unable to do so now because I now have two .ldf files.
So now I'm stuck with one 30GB ldf file on a filled drive and a 1MB file that probably has nothing in it on another drive that I cannot get rid of.
I made a last ditch effort and ran:
DBCC SHRINKFILE('fileName', EMPTYFILE)
USE master
GO
ALTER DATABASE DatabaseName
REMOVE FILE LogFileName
GO
...without success. I tried using the DBCC SHRINKFILE then deleting the log file in Enterprise Manager --> Database properties --> transaction logs
...but was given an error message that my logs were full and they needed to be backed up. The logs were backed up a few hours back but no space was freed. Whenever I try to use the alter database + remove file, it would tell me the file's not empty. When I use the Enterprise Manager it would tell me the transaction logs are full.
I'm in a hole here without any ideas as to how I can rid of the second ldf file to use my last resort. Any ideas would be appreciated. Thanks.
February 23, 2006 at 12:15 pm
try
backup log [yourdb] with truncate_only
dbcc sqlperf (logspace) -- returns log size/usage..
immediaelty do full db backup after above log backup .. (the logs are now invalidated)
dbcc shrinkfile ([logical_file_name], sizeMB)
backup log
detach_db then delete .ldf files.. then re_attach..
should create single.ldf for your detached db after you attach.
set your log size to something reasonable.. (large enough for your data to "grow into")
good luck
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply