March 22, 2002 at 9:27 am
I have inherited a SQL 2000 database that has 2 transaction log files defined for it. I would like to eliminate one of the transaction log files since the first one that was defined has plenty of disk space and the second file that was defined is on a disk with limited space. When I try to delete the file I get an error that is is not empty. I have tried to truncate the file and shrink the file, but I still cannot empty it. Does anybody have any info for me on how to clean up the transaction log files?
March 26, 2002 at 5:10 pm
I believe you must first perform a backup in order to truncate the log. Then you should be able to remove it.
March 26, 2002 at 5:49 pm
Try
BACKUP LOG [dbname]
WITH TRUNCATE_ONLY
this should free up the space in the log so you can delete the secound file.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 26, 2002 at 6:06 pm
Try dbcc shrinkfile with the emptyfile parameter. This removes any data and tells SQL not to use the file further. You can then remove it.
Andy
March 29, 2002 at 8:48 am
Thank you for the feedback. However, the Backup Log option did not free up the space enough to delete the second file, and the DBCC shrinkfile option also did not allow me to delete the second file (empty file param only seems to work on data files, not log files). I did however, find a solution. The DUMP TRANS <db> with NO_LOG (after a full db backup) cleared the transaction log and allowed me to delete the second file.
March 29, 2002 at 9:20 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply