Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Sep 6 '09 at 19:31

This question came from our site for professional and enthusiast programmers.

    
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

2 Answers 2

up vote 4 down vote accepted

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

share|improve this answer
    
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...).

share|improve this answer

Your Answer

 
discard

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.