• Carlosql (7/27/2013)


    My plan is:

    1. Stop the replication jobs

    2. change to Single-user mode

    3. move the log files

    4. restart the sql server.

    5.change to muti-user mode

    If you do that to the system databases, SQL won't start (though you won't be able to move the system DB's log files with SQL running). You do that to user databases, they'll come up recovery pending (though you won't be able to move the log files with SQL running)

    The correct procedure for moving databases' log files does not involve single user at all. Other than for the system DBs (which you may want to leave alone), the procedure is:

    Run ALTER DATABASE specifying the WITH MOVE to change the metadata saying where the log files are

    Take the database offline (works fine for replicated DBs)

    Move the actual files

    Bring the database online.

    You can do similar with TempDB, which is probably the only system DB that moving the files for is important (maybe MSDB), but with a restart of SQL instead of taking the DB offline.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass