1:00 AM
Paul B
You may have noticed the lack of new content recently around here. If you have read any of my recent posts you may have gotten the idea that I have been pretty busy at work. if you guessed that, you are right! It was like that around here when i first started working where I work, but things settled down and I was able to write more regularly again. I’m sure that will happen soon. It’s the blogger circle of life really.
So I was thinking of stuff to write recently and I was suffering from some writer’s block. That is when I thought about writing about something I had to do recently at work. So here is the back story. We had a Microsoft office SharePoint Server (MOSS) 2007 farm running with a SQL 2005 back end. The data drive on the SQL server was running out of disk space. When i say running out of disk space, I mean I would log in and there would be 0MB left!
I would shrink the databases, and files, truncate logs, and all the usual tricks. That would only free up a few hundred MB of data. Not really anything to write home about. What made matters worse was this SQL server was physical, and all the drive bays were filled. I couldn’t add any more drives. Something had to be done.
I decided to install a new NIC, and connect the server to our iSCSI SAN and provision a LUN for use on the SQL server. The problem was though that I now had to move one or more databases from the drive that was full over to the SAN storage. Here is how I did it!
NOTE: I recommend you take a full backup of your environment before doing this. It is pretty easy to jack this up if you are not careful. I accept no responsibility if you “screw the pooch” on this.
The first thing you will have to do is identify the databases you want to move. I recommend looking at the biggest ones first, as they are what is probably filling up your drive the fastest. That’s what I did anyway. I also tried to pick one that wasn’t used as much.
Now you need to go over to your SharePoint server and run the following command to detach your database from SharePoint. I realize that the command looks like the database is getting deleted, but it isn’t.
stsadm -o deletecontentdb -url http://<your-site's-url> -databasename <Database name>
Repeat this process for each database you are moving. If you can’t find stsadm.exe it is usually located in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\BIN. I recommend copying it to c:\windows\system32.
Once the database(s) is/are detached from SharePoint, on your SQL server go to each database you want to move to the new drive, then right click, click on Tasks> Take Offline.
Once the database is offline, right click the database again and select Tasks> Detach.
No browse to your SQL data directory and locate the .mdf and .ldf database files. If you didn’t know, the .mdf file is the actual database flat file, and the .ldf is the log for the database. Now move them to your new drive or partition. I recommend creating a new folder called SQL with all the same SQL data folders. Once moved go into SQL, right click on Databases and select Attach. Click the Add button and browse to the .mdf file(s) you just moved. You will have to do this one at a time for each .mdf file. Also, depending on what OS you are running, you might have to turn off UAC. Click OK, and you are just about done.
Now all that is left is to re-attach the database to SharePoint. To do that, go back to your SharePoint server and run the following command:
stsadm -o addcontentdb -url http://<your-site's-url> -databasename <Database name>
Once attached, browse to your site’s URL to make sure nothing is broken. Chances are that if you followed this post, you will be good to go!
Have you ever had to do something like this? Are you running into a similar issue? How did you solve it? Did you do something differently? Let us know in the comments.
