September 23, 2008 at 7:53 am
I want to move the LDF files of ALL my system databases and 1 or 2 user databases to another drive.
I am not using attach/detach, but rather:
ALTER DATABASE DBName MODIFY FILE (NAME = logicalLogFileName, filename = 'newPath\LogFileName.ldf')
I am opting for the ALTER statement instead of the attach/detach, cause only 1 (ldf) file is moved.
Thing is - this is a cluster server. My question is: will this being a cluster server, make any difference?
Will my ALTER statement work just as well?
What about the master.ldf - any specifics there that I need to be aware of?
September 23, 2008 at 7:59 am
Your plan sounds fine. So, you are going to run the alter database command, stop the SQL Server and Agent services, move the log files and start them back up. Correct? If so, then that should go fine.
As for the master file, just follow the directions in BOL and things should go well. Being that it doesn't really use the log file, I'm not sure that I would go through the trouble of moving that file unless having an ldf in the midst of the mdf's keeps you awake at nights. There was a time that it would with me so don't feel bad. 🙂
As always, don't walk on a tightrope and go without having some backups that you could recover from if needed. You may think that is crazy for a file move but.....
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 23, 2008 at 8:05 am
Hi,
Yes, the sequence of ALTER, stop the SQL Server and Agent services, move the files and start service is just what I had in mind.
As for the master - it's all about conformity and the client would like all ldf files on the same drive.
DB Backups always at hand 🙂
Thanks for your quick reply
September 23, 2008 at 8:41 am
It should work fine the way you are doing it. I think you might need to stop both nodes to get this to work and prevent failover.
September 23, 2008 at 9:01 am
You don't need to stop SQL and the agent after doing the alter (not for the user databases). Just take the database offline after doing the alter database, move the file and bring it online again.
TempDB's file move will take effect after a SQL service restart. You don't need to move the files manually.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply