One of our customer recently purchase new hard disk for their database server, and need to place all SQL Server transaction log files to that new hard disk. As far as i know, SQL Server transaction log file records every transaction that was executed in a database. And i think to placing transaction log files in separated location will allow disk to work optimally. These are the steps to move SQL Server transaction log files :
Determine current database files location and transaction log file location and then record the location, it will be used later when I re attached the database using the following command :
USE <databasename> GO EXEC sp_helpfile GO
The above command will return result as the following sample :
Take a note for primary database file location and log file location. Then ensure that no one connect to database, or if there still any user connected, you can kill the connection using procedure on the other article, then we are going to detach the database. From SQL Server Management Studio Object Explorer, expand Databases, right click database name you want to detach, point to Tasks and click Detach…
On Detach Database page, ensure that the status is Ready, then click OK to continue Detach process. If the status is Not Ready, you can found the reason under the Message column.
Once Detach process has completed, move the transaction log file from the old location to the new location using windows explorer. Ensure that directory permission on new location is same with permission on the old location. Then go to SQL Server Management Studio, from object explorer, right click on Databases and click Attach.
On Attach Database page, click Add button, and navigate to database file location, select Database file name and click OK
When return to Attach Database page, you will notice a message that log file was not found. Click browse button (…) then navigate to the new log file location, select the log file and click OK
After select the log file, click OK button again to Attach the database. At this point, this database should be back online. Verify the database file and transaction log file location using the above SQL command.