Partition OF MDF File

Question

Partition OF MDF File

SQL Editor

SSCommitted

Points: 1980
More actions
November 25, 2009 at 1:33 am

#156441

Hi All
I Have My Database Approx 10 GB Of MDF File And 2 GB Of LDF File.
NOW I Want Mange this Database in Multiple Data file with maximum 1 GB Size Of each. And Want Automatically Create New NDF File When Previous File Out Of 1 GB Size.
How This Possible.
Thank In Advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

joeroshan SSChampion Points: 10377 More actions · Answer 1

You can create different file groups, add data files for the file groups. then you can move objects across file groups. But won't it be hard for you to manage too many data files ? Is there any particular reason for you to do this?

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

SQL Editor SSCommitted Points: 1980 More actions · Answer 2

thank for reply

Copy File One Place to other take more time. and some time it corrupted due to network problem.send this file using ftp is also painful work.we can not send this file in dvd. these are issue.

please suggest me how to mange this file in multiple ndf files.

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 3

Explanation is understood, still, each data file of 1 GB may cause way too many files.

SQL Server does not create a data file automatically. You need to do a custom task that either monitors your data files and create a new data file when it reaches the size of 1 GB.

OR

create multiple files initially and set autogrow off to all of them except one (may be the last file) and give Max size of 1 GB or whatever you want, so that if you create multiple files now, SQL will proportionally fill them and you can come back and check the sizes / free space available on those data files to create few more.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

joeroshan SSChampion Points: 10377 More actions · Answer 4

I copying is the problem, you can take a striped backup. Though its not advisable for regular backup plans (I corrupt file make whole set useless),

you can use this for occasional db movement.

backup database databasename to disk='firstfile', disk='secondfile',disk='firstfile' .........

You must need the whole set to do a restore

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

dba_pkashyap Hall of Fame Points: 3336 More actions · Answer 5

If backing up the file and sending is the prooblem, then use the best ZIP/compressor and then compress the backup file, which may be around 10-20% of your original file.

You have many s/w 's to split a single file into multiple files, which you can implement and make a dvd of it and send it over.

Pavan.