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?

  • This question would be more appropriate on Server Fault. – Jerry Bullard Sep 4 '09 at 17:16
  • Ah - you're right. I'm still not in the habit of considering that! – SqlRyan Sep 4 '09 at 17:36

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

  • As much as I dislike this solution, it works without any downtime, so that's what I'll go with. It still seems like SQL Server should offer some method to change the "primary" log file while you're online, but it does not. Thanks for your help! – SqlRyan Sep 4 '09 at 20:09

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...).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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