Move log file without taking database offline

Question

I need to move a database log file to a new partition without taking the database offline.

The normal way of doing this would be to detach the DB, move the log file then reattach the db.

Is it possible to do this without taking the database itself offline and if so how?

• Vadzim: See also dba.stackexchange.com/questions/96457/moving-db-log-destination

Answer 1

There is no way to do this with an online database.

When you move a database file (ALTER DATABASE ... MODIFY FILE), you even get the following message:

The file "YourFile" has been modified in the system catalog. The new path will be used the next time the database is started.

The normal way of doing this would be to detach the DB, move the log file then reattach the db.

That's not the "normal" or accepted way I would do it. To move database files, I do the following:

1. Run an ALTER DATABASE command to change the location of the file(s)
2. Take the database offline
3. Physically move the file(s) to the new location specified in step #1
4. Bring database online

See this reference on TechNet: Move User Databases

Answer 2

According to the reference on TechNet Move User Databases from Thomas Stringer's answer, if you want to move the files without stopping the whole SQL Server instance (following the "Planned Relocation Procedure"), the order should be:

ALTER DATABASE database_name SET OFFLINE;
... move the file(s) to new location
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
ALTER DATABASE database_name SET ONLINE;

Note the OFFLINE first; afterwards move the files and tell SQL Server about the new location(s).

If you do need to take the whole SQL Server instance down (see the procedure for "Relocation for Scheduled Disk Maintenance"), it is best to modify the file locations first so that when the instance is restarted, there is no issue finding the files in the new locations.

Answer 3

I know this is an old question but can't you just add a second log file (which is then used by the DB instead of the original) in the new location then shrink/remove the original?

Answer 4

This is what we've done:

1. Add a secondary transaction log file
2. Set the first transaction log file's auto-growth to NOT grow
3. Run the following

(essentially shink's the original log file with the EMPTYFILE parameter, then removes that file)

USE [DatabaseName]
DBCC SHRINKFILE (N'LOGFILENAME1' , EMPTYFILE)
GO
ALTER DATABASE [DatabaseName] REMOVE FILE [LOGFILENAME1]
GO

I have gotten the following error when running this, but running the above again seems to get around the issue

DBCC execution completed. If DBCC printed error messages, contact your system administrator. Msg 5042, Level 16, State 2, Line 4 The file 'LOGFILENAME1' cannot be removed because it is not empty.