Sql-server – SQL Server – remove a primary logfile without downtime

database-backupsql server

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?

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

Related Topic