July 16, 2008 at 11:44 am
Hi All,
I am running out of space with my Datafile .mdf, I don't have enough free space on my C:\Drive, how can I do to move files onto my D:\Drive....also is there any posibility to create a new Datafile??..can I do that when I get out of space?
Please any help???
Thanks
July 16, 2008 at 12:14 pm
You can create a new data file, then you need to move existing tables or indexes to the file as only new tables would be created there otherwise.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 16, 2008 at 3:50 pm
hi yes you can move the files and also add new files
First set the database offline.
Copy the current disk files to the new location.
Use the "ALTER DATABASE MODIFY FILE" statement to register the new file locations.
Add any other new data files required using "ALTER DATABASE ADD FILE".
Bring database back online.
You mention moving from C to D, doesnt sound like your using an appropriate Disk config so i would think adding extra disk files will not pay any dividends and will only confuse matters for you. If my assumption is incorrect please forgive me.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 16, 2008 at 10:39 pm
Could you tell us what is the data file size of your database.
July 16, 2008 at 11:38 pm
[font="Verdana"]Why cant you try detach \ attach method. Try the steps below
*) Take the logins which has this db as default db
SELECT name from sys.syslogins where dbname='dbname'
*) Detach the db
*) Move the physical files to D: drive
*) Attach the files to sql server
*) Map the default db's for the login again.
But this method requires downtime![/font]
Regards..Vidhya Sagar
SQL-Articles
July 17, 2008 at 2:01 am
Am with Jack 😀 ..Thats the way you can do ...
Create a secondary datafile on your D drive and then move some tables to that location
July 17, 2008 at 7:09 am
Hi All,
My .MDF datafile has 33.0MB, used size 32.9MB, what I tried to do is create a new .ndf datafile as PRIMARY, but I found that my C:\Drive is running out os space also, so I have to move my datafiles to the D:\Drive, it is new, so it has
170GB for use...
Where can I find an step by step doc to help me with this procedures?..I am really new using SQL Server, I have been working on Oracle, and it looks a little bit different... Please any suggestion?
Thanks
July 17, 2008 at 7:35 am
Detach your MDF files copy them to D drive and attach them.
Hope this will help
http://msdn.microsoft.com/en-us/library/ms190794.aspx
Run dbcc checkdb after detach, attach
July 17, 2008 at 7:41 am
Interesting your mdf file is 38 ~ 39 MB and your C drive is out of space so the D drive has 170 GB!!! What do you have in C drive.
The things that you should do are detach your database move the files to the D drive and attach again you DB but you should specify you current location of your mdf (data file) and ldf (log file) both of them!
:w00t:
July 17, 2008 at 10:38 am
you don't need to detach the database
set the database offline using
ALTER DATABASE mydatabase SET OFFLINE
you can then do what you like with the disk files (even delete them). Move the required files to their new location(s) and run the following T-SQL for each file being moved (obviously substitute name and filename for your database file details)
ALTER DATABASE mydatabase MODIFY FILE ( name = perry_log, filename = 'D:\SQL\perry_log.ldf')
Once this is done a message will inform you the system catalog has been updated with the new paths and will be in force next time the database is started. Start the database using
ALTER DATABASE mydatabase SET ONLINE
A separate filegroup is not going to benefit you at all, ideally filegroups should be placed on separate arrays, something it sounds like you don't have. Keep things simple rather than complicating them more and you'll be fine
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply