Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've got a large db (called Navision4) which currently has 2 log files:

Navision4_Log Navision4_Log3

There was a log called Navision4_Log2, but i managed to remove this.

Navision4_Log is located on the C:\ drive - and is not used. Navision4_Log3 is located on the G:\ drive - but is huge - and this appears to be where all transactions are written.

However, I want to remove one, leaving just one log file.

How should I do this? I tried to remove Navision4_Log - but got:

Error 5020: The primary data or log file cannot be removed from a database

share|improve this question

What recovery model is your database using? are you taking regular log backups? If your in full recovery & not taking log backups, then this may explain why Navision4_Log3 is so large. You should fix this by either taking log backups, or switch to simple recovery.

I would move Navision4_Log to the G drive rather then try to remove it. My guess is that Navision4_Log3 exists because the server was running out of space on C: & the admin didn't know how to move it.

To move the log file to G: use the following:

--move log file to G:

EDIT:SQL Server 2000 doesn't support those options, so you'll have to use sp_detachdb & sp_attachdb, you can also detach & attach in the Enterprise Manager GUI. Just make sure you attach the db & specify the owner as sa.

Then you can remove Navision4_Log3 if you wish. What you'll need to do (if your in full recovery) is take a log backup, then shrink the log using DBCC SHRINKFILE('Navision4_Log3', EMPTYFILE) You may need to take several backups & do several shrinks before the file will empty out & then you should be able to remove it.

share|improve this answer
Thanks for this excellent answer... This DB is also a replication publisher - does this affect me detaching / re-attaching? – alex Nov 30 '09 at 11:04
no it shouldn't – Nick Kavadias Nov 30 '09 at 11:26

Since it is a replicated database, you will have to remove replication before detaching. SQL Server 2000 will not allow you to detach a replicated database.

After that Nick is correct, detach the database and reattach with only the only file you wish to use on the G:

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.