December 4, 2009 at 10:49 pm
hi friend
in my organization we are maintain Production server that will run 24*7
that is sql server 2000 and data files present in c drive
now c drive almost full.. i need to shift the data file to another drive but no down time
please any one help for this problem
Thanx
yogi
December 7, 2009 at 9:53 am
There's no real way I can think of to do it with NO downtime, but it's quite easy to manage with minimal downtime.
You're going to have to take the database offline to move the mdf file, so then it's just a case of moving/copying the file, then reattaching in SQL Manager or you can take a full backup and then use a "With Move" statement on the restore operation to specify where you want the files after the move (I would still advise taking the db offline for the backup so no new transactions go through after the backup takes place prior to the move, but either way should take just a few minutes max dependant on the size of the db).
December 7, 2009 at 10:12 am
Have you not thought about this solution --- adding additional storage space and add secondary Data Files to that Database and disabling the autogrowth of your data files residing on the C drive.
It does not need any downtime.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 7, 2009 at 1:01 pm
I agree with Bru, but once the immediate emergency is resolved you should plan to move the database files off of the C drive. There is already enough I/O contention on that drive already with OS operations. Ideally you would want mdf files on one drive, ldf files on another, and possibly tempdb files on yet another drive.
Joie Andrew
"Since 1982"
December 7, 2009 at 1:11 pm
Thats true Andrew, and it all begins with a well planned implementation.
But sometimes, it so happens that we have to inherit a database that has been implemented by someone else, I guess the OP is in such a situation.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 7, 2009 at 8:53 pm
Thanks bru
can u explain briefly how to do with examples ..please
December 7, 2009 at 9:29 pm
yogaanand.me (12/7/2009)
Thanks brucan u explain briefly how to do with examples ..please
Examples on what? To add storage go got to ask your Storage / Network / Systems Engineer. They would be the ones who are going to help you out. Once the new Drive / LUN is added, then your tasks on the database starts.
On the Database, you got to identify what are the files residing on the C Drive for that particular database. Add an additional database file(s) on the New Drive for this database. After adding the database file, disable the autogrowth option of the other database files of the same database. That would ensure any data added would be stored on the database files in the new drive.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply