[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 883
  • Last Modified:

What is the best way to change the default path for SQL 2005 Transaction logs?

I'm setting up a new SQL 2005 server to migrate all of our SQL 2000 databases to.
I would like to have the databases and transactions logs on separate drives. I changed the default installation path for the databases during installation to the d:\program files..... directory.
I would like to have all the transaction logs go to the c drive.  I created a test db and used the "alter database ...move file" command to move the transaction log to the c drive. It said that it competed succesfully. However,when I restarted SQL server, the file was not moved and I can no longer access the database. I get a "access denied errror".    I would like to get this resolved before I start moving the production databases over to the new server.
0
City_of_Del_Mar_IT
Asked:
City_of_Del_Mar_IT
3 Solutions
 
matrix_aashCommented:
You can achive this if you use the attach option by right clicking the databases in Microsoft SQL Server Management studio and select the mdf file and ldf file and change the location of each file to wherever you want to save them

Hope this helps.

Aash.
0
 
Chris MangusDatabase AdministratorCommented:
Don't forget to change the location for MODEL database also so any newly created databases will have LDF files go to the new locations.

I'd also suggest not putting your LDF files on your C: drive.  Windows uses that drive heavily, especially in writing to the swap file.  You'll likely not experience much, if any, performance improvement by putting any MDF or LDF on a C: drive.
0
 
Scott PletcherSenior DBACommented:
>> created a test db and used the "alter database ...move file" command to move the transaction log to the c drive. It said that it competed succesfully. However,when I restarted SQL server, the file was not moved and I can no longer access the database. <<

The ALTER commands only change the internal SQL table(s).  After stopping SQL, you must still physicall move the files to the new location before restarting SQL.

You do also need to move the log file for model to the new location if you want all logs in the future to go there.  You can issue the ALTER command and do physical moves for the model db just like you do any user db.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now