Home / server / Questions / 62871
Accepted
SqlRyan
Asked: 2009-09-05 09:12:27 +0800 CST

SQL Server - remove a primary logfile without downtime

  • 772

I have a MDF and LDF on the same drive, and the drive is almost out of space. I have a second drive in the same server, and I'd like to move the LDF file to that drive. I know that I can detach and re-attach, but that involves downtime, and I'd like to do this transparently.

  1. I created a second log file on my new drive and added it to the database, so the DB has two log files.
  2. When I tried to remove the old log file, I get "Unable to remove primary log file"

All the research I did suggested that there's no way to change the "primary" log file on an online database - you have to detach it, and reattach it with just the logfile you want as primary. In an ideal world, here's how I picture that it should work:

  1. Create a second log file and add it to the database
  2. Set the new file as "primary", so all new log data is written there
  3. DBCC SHRINKFILE, checking the option to migrate all data to other files in the same group
  4. As old "primary" file is now empty, you can delete it from the database

Am I crazy? Is there a way to do this that I'm not aware of?

sql-server database-backup

2 Answers

  1. Best Answer

    I would do like this:

    1. Backup database
    2. Create new log file
    3. Run shrinkfile on the old database
    4. Set the Initial Size of old Log File to 1MB and Authogrowth to None.

    You will have two log files, but one will have only 1 MB.

    Hope it would help. I hope there is some better way to do that

    • 4

  2. As well as Lukasz' answer

    You can't delete or empty a log file like a data file. You need downtime to move it.

    However, now you have 2 log files. You can RESTORE .. WITH MOVE.. to ensure both are on the drive you want. If you want to delete one, then you have to detach, delete, attach (which is not recommended...).

    • 1