How to move your SQL Server transaction log?

Why would you move your transaction log file?  Maybe your tran log has outgrown the disk that it is on, and you need to move to another location to prevent failure.  Or, you may be moving the log to another physical drive, separate from the data, to improve your I/O performance.  Regardless of the reason, this can be done via SSMS (SQL Server Management Studio) or tSQL, and I will show you the tSQL approach in this tip.  Be sure to complete these steps beforehand:

  - Use sp_helpfile to note the current location, size and name of the existing files.
  - Determine the location and name of the new file location. 
  - Backup your database.

Ok.  This is how you move your tran log, adjusting your drive letter and directory names accordingly:
 
     /* Take exclusive access to your database. */
     ALTER DATABASE YourDatabaseName
     SET OFFLINE WITH ROLLBACK IMMEDIATE;

     /* In Windows Explorer, copy/paste your files to new location, delete the old files. */

     /* ALTER your database, use MODIFY FILE for new file location. */
     ALTER DATABASE YourDatabaseName
     MODIFY FILE (
     NAME='YourDatabaseName_Log',
     FILENAME='X:\NewDirectory\NewSubdirectory\YourDatabaseName_Log.ldf');

     /* Bring your database back online. */
     ALTER DATABASE YourDatabaseName
     SET ONLINE;
  
That's it!

But what about sp_detach_db and sp_attach_db?  The relocation of the transaction log can be done with sp_detach_db/sp_attach_db in SQL Server versions pre 2012.  These commands were deprecated, however, in v2005.  If you are in v2005 or v2008, the file relocation can be done using this method, again adjusting the drive letter and directory names accordingly:
 
    USE MASTER
    GO
    /* Take exclusive access to your database. */
    ALTER DATABASE YourDatabaseName
    SET SINGLE_USER
    GO

    /* Detach your database. */
    EXEC sp_detach_db 'YourDatabaseName'
    GO

     /* In Windows Explorer, copy/paste your files to new location, delete the old files. */

    /* Re-attach your database. */
    EXEC sp_attach_db 'YourDatabaseName',
    'X:\NewDirectory\NewSubdirectory\YourDatabaseName_Data.mdf',
    'X:\NewDirectory\NewSubdirectory\YourDatabaseName_Log.ldf'
    GO