How to move transaction log file (.ldf)

Is your drive out of space because your database is growing? Then you might think to move the transaction log by using SQL Server stored procedures for detaching and attaching the database.

For example, to move the pubs database from drive C to drive D:

  1. First use the following sp_detach_db command to detach the database
    EXEC sp_detach_db 'pubs'
  2. Next, copy the pubs_log.ldf file to the destination drive.
  3. Finally, after you copy the file, you can use the sp_attach_db stored procedure to reattach the data to SQL Server.
    EXEC sp_attach_db 'pubs', 'c:\mssql\data\pubs.mdf', 'd:\mssql\data\pubs_log.ldf'

Is this useful?