Moving SQL 2005 mdf and ldf files 1

[ousoonerjoe]

Is there a way to move the database files (mdf, ldf) while a database is ONLINE and in Use?

I have found where it can be moved offline via a Detach-Attach or an ALTER statement, but both methods require the database to go offline.

If it is not possible in 2005, what about 2008? 2010?

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[gmmastros]

Can you please explain a little more?

Are you trying to move the mdf/ldf to another disk on your server?
Why do you want to move the files?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Yes. The new raid system ordered supports a lot more space and performance than the compressed drive the database currently resides on. After many months of pushing to get the database off the compressed drive, the green light was given to the move. Space has been allocated, now the physical move of the data files (mdf, ldf) must be relocated to the new physical location.

The question was in reference to the move, if it could be done ONLINE or if this is an OFFLINE process only.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[gmmastros]

Moving the mdf and ldf files cannot be done while the database is online.

There are ways to accomplish this without experiencing any down time. These methods can be a bit complicated though. For example, you could set up mirroring on your database. Once the data is copied to the mirror, you can take down the database so that the mirror takes over. move the mdf & ldf and then bring it back up and resynchronize the transactions that occurred while the db was down.

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/move-databases-to-new-server-with-little-1

Alternatively, you could create a new file group on your new disk, and then move your tables to it. This is done by rebuilding your clustered indexes on to the new filegroup. This wouldn't really move the mdf and ldf, but it would effectively move your data to the new disk.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

I figured that was the case.

Do you know if there are any plans in newer/upcoming releases of SQL Server to support a file move while active?

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[gmmastros]

I don't know. I can't imagine a situation where this would be the case. I mean... when you save data (insert/update/delete), SQL writes to the log file and then to the data file, and then back to the log file. This is done to maintain the integrity of your data. I can't imagine how you could use a SQL database without being able to write to the disk.

What I would do (if I were in your situation).... I would determine the best time to move the data file. This would happen to be when there is the least amount of activity. I would take the DB off line, move the MDF file and then reattach the DB. Now that you have multiple disks in your server, you may want to consider leaving the LDF file on your existing drive and only move the ldf.

Again, think about what happens when data changes in your database.

First, the log file is written to... basically saying, "I plan on changing this data". Then, the data is changed in the table. Finally, the log file is written to again saying... "I'm done changing the data". This involves 3 writes to 2 separate files. The drives must reposition the disk to the correct file before the write can occur. When you have the data and log on separate disks, there is less repositioning of the drive heads and you end up with better performance.

Just curious... how big is your MDF file?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

It's only 2 GB on the primary database. The original configuration of the server only allowed for 10GB for all databases (including Report Server). Needless to say, they compressed the drive to make space, but that started causing deadlock issues and free space errors. I've been hounding them to get a real raid system in here with real drives to get the databases moved to.... 2 years later, we finally have the hardware.

I inherited a real mess from the guy I replaced. He was a network guy that thought he knew databases and programming.

Thanks for the feedback, George.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------