Sql-server – Re-assigning / changing the Primary Log File – SQL 2000

log-filessql server

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

Best Answer

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:

ALTER DATABASE Navision4 SET OFFLINE;
--move log file to G:
ALTER DATABASE Navision4
MODIFY FILE (
NAME='Navision4_Log',
FILENAME='G:\MSSQL\TLOG\Navision4_Log.ldf');
ALTER DATABASE Navision4 SET ONLINE;
**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.

Related Topic