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.
- I created a second log file on my new drive and added it to the database, so the DB has two log files.
- 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:
- Create a second log file and add it to the database
- Set the new file as "primary", so all new log data is written there
- DBCC SHRINKFILE, checking the option to migrate all data to other files in the same group
- 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?